DB size misreporting problem

[Pete Mylo]

I would like to ask a question about the problem I encountered with MS SQL Server v6.5 running on WinNT4 server, with database 500MB in size, 2-5% data changed a day, 2% new data added a day. The problem concerns misreporting the actual available space in the DB info in the Enterprise Manager. The device for DB is set for 500MB, transaction log is 100, the actual size of all data up to now is around 425MB, so there should be around 75MB available for DB data. I wanted to see the actual available space, using the Recalculate button in the DB info window. Up to that moment EM reported 90MB of space still available. Right after pressing the recalculation button Enterprise Manager started to report 0.0MB available space, while the DB is fully functional and the DB would need another three months to fill to 500MB. So obviously there is a free space, but I am not able to see it. another pressing of recalculation button does nothing. What could I do to make EM to report the DB free space properly instead of feeding me with constant zero?
Note: sp_spaceused shows the correct number, 175 for DB+Transaction.

-Pete-

[Maryanne]

Pete try using the dbcc checktable(syslogs) command then try checking the space again and see if the amount reported is more accurate. I have similar problems with several tables that are increased daily.

Thanks
Maryanne


------------
Pete Mylo at 6/4/01 6:02:45 AM

I would like to ask a question about the problem I encountered with MS SQL Server v6.5 running on WinNT4 server, with database 500MB in size, 2-5% data changed a day, 2% new data added a day. The problem concerns misreporting the actual available space in the DB info in the Enterprise Manager. The device for DB is set for 500MB, transaction log is 100, the actual size of all data up to now is around 425MB, so there should be around 75MB available for DB data. I wanted to see the actual available space, using the Recalculate button in the DB info window. Up to that moment EM reported 90MB of space still available. Right after pressing the recalculation button Enterprise Manager started to report 0.0MB available space, while the DB is fully functional and the DB would need another three months to fill to 500MB. So obviously there is a free space, but I am not able to see it. another pressing of recalculation button does nothing. What could I do to make EM to report the DB free space properly instead of feeding me with constant zero?
Note: sp_spaceused shows the correct number, 175 for DB+Transaction.

-Pete-

[Pete]

The syslogs table concerns basically the transaction log. I need the Enterprise manager to show the available size for data (which is now zero), it already does show the log available space correctly. Anyway, I tried it and the result was more available space in the transaction log, as I anticipated.
I wonder if extending the DB could help EM in reporting the proper size, by entering new values. Any other ideas? Maryanne, thanks for your help anyway...

-Pete-

------------
Maryanne at 6/4/01 4:25:23 PM

Pete try using the dbcc checktable(syslogs) command then try checking the space again and see if the amount reported is more accurate. I have similar problems with several tables that are increased daily.

Thanks
Maryanne


------------
Pete Mylo at 6/4/01 6:02:45 AM

I would like to ask a question about the problem I encountered with MS SQL Server v6.5 running on WinNT4 server, with database 500MB in size, 2-5% data changed a day, 2% new data added a day. The problem concerns misreporting the actual available space in the DB info in the Enterprise Manager. The device for DB is set for 500MB, transaction log is 100, the actual size of all data up to now is around 425MB, so there should be around 75MB available for DB data. I wanted to see the actual available space, using the Recalculate button in the DB info window. Up to that moment EM reported 90MB of space still available. Right after pressing the recalculation button Enterprise Manager started to report 0.0MB available space, while the DB is fully functional and the DB would need another three months to fill to 500MB. So obviously there is a free space, but I am not able to see it. another pressing of recalculation button does nothing. What could I do to make EM to report the DB free space properly instead of feeding me with constant zero?
Note: sp_spaceused shows the correct number, 175 for DB+Transaction.

-Pete-

[Simon]

Pete,

Inaccurate free space reporting is a well known 'feature' of SQL 6.5 Enterprise Manager. 6.5 is not so good at keeping internal system tables up to date, you need to use the DBCC commands to do this. sp_spaceused is advised as more reliable, however this can be inaccurate also. Check the Transact-SQL Help for 'DBCC Updateusage' which is one way to correct this.

I have this problem on a table that has a lot of pages (several hundred thousand)deleted weekly. The syslogs entry on the sysindexes table on the user database is not updated after the data is deleted. Even sp_spaceused reports incorrect space usage, until this syslogs entry is updated. As Maryanne advised, dbcc checktable (syslogs) will correct this. This is how I fix it. sp_spaceused will report correct values immediately, EM seems to take longer.

Basically, don't rely on what you see in EM !

------------
Pete at 6/5/01 12:23:52 AM

The syslogs table concerns basically the transaction log. I need the Enterprise manager to show the available size for data (which is now zero), it already does show the log available space correctly. Anyway, I tried it and the result was more available space in the transaction log, as I anticipated.
I wonder if extending the DB could help EM in reporting the proper size, by entering new values. Any other ideas? Maryanne, thanks for your help anyway...

-Pete-

------------
Maryanne at 6/4/01 4:25:23 PM

Pete try using the dbcc checktable(syslogs) command then try checking the space again and see if the amount reported is more accurate. I have similar problems with several tables that are increased daily.

Thanks
Maryanne


------------
Pete Mylo at 6/4/01 6:02:45 AM

I would like to ask a question about the problem I encountered with MS SQL Server v6.5 running on WinNT4 server, with database 500MB in size, 2-5% data changed a day, 2% new data added a day. The problem concerns misreporting the actual available space in the DB info in the Enterprise Manager. The device for DB is set for 500MB, transaction log is 100, the actual size of all data up to now is around 425MB, so there should be around 75MB available for DB data. I wanted to see the actual available space, using the Recalculate button in the DB info window. Up to that moment EM reported 90MB of space still available. Right after pressing the recalculation button Enterprise Manager started to report 0.0MB available space, while the DB is fully functional and the DB would need another three months to fill to 500MB. So obviously there is a free space, but I am not able to see it. another pressing of recalculation button does nothing. What could I do to make EM to report the DB free space properly instead of feeding me with constant zero?
Note: sp_spaceused shows the correct number, 175 for DB+Transaction.

-Pete-