Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
 Posted - 2006-06-20 : 10:52:54
|
| If both the datafiles and the transaction log are set to unrestricted growth, why would space available be 0:00 MB? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-20 : 11:01:02
|
| Wherever you are getting this from it is probably the space free in the files allocated.That value looks like it's not correct or you have trouble.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2006-06-20 : 11:22:06
|
| I don't understand it.The space allocated is exactly equal to the sum of the data and tlog size. I can shrink the database successfully, freeing up space, but it still says space available 0:00 MB |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-20 : 12:08:15
|
| If this is from enterprise manager then that was a problem on large databases in v7. I thought it had been fixed but maybe not.What does sp_spaceused say.and try updating usage==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2006-06-21 : 06:18:37
|
Thanks for your thoughts.sp_spaceused returns:
Database_name | Database_Size | Unallocated SpaceHIC | 1732.25 MB | -26532.23 MB
Reserved Space | data | index Size | Unused28351664 KB | 419712 KB | 27903840 KB | 28112 KB
1. How can the Unallocated space have a negative value?Here are some observations:2. There has been legitimate increased activity on the DB (a busy box office) and as a result some of the tlog backups have been larger than usual, larger than they should be though. None the less, tlog backups are performed frequently every 15mins.3. I perform a Complete Back Up at 02:00. The first TLog backup occurs 06:00. This first TLog backup is 800mb. Why would it be so big? Could this be empty space? If so, is it good practise to schedule shrinks immediately after Complete Back Ups?4. I'll try updating usage out of hours. |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2006-06-21 : 08:43:19
|
| updateusage brought back line after line and has corrected the problem.So how often does everyone run dbcc updateusage, and on what databases?I'd appreciate it if someone could still answer point 3 in the previous post above. |
|
|
|
|
|
|
Space available to a database