DB available size misreporting

[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 (plus 100 for transaction), 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, then 100 for transaction, 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 avail. for DB+Transaction (tran. is almost empty). I also tried the dbcc checktable(syslogs), it obviously aplied to transaction log only.

-Mylo-

 

[Bernadette]

Hi There

I have found that EM does not report the "actual" trans log size correctly all the time. So what I usually do is check my database/trans log size using the following script in query analyser. It is much more accurate than EM.


/* THIS SCRIPT SHOWS THE REAL TRANSACTION LOG SIZE */


dbcc checktable(syslogs)
go
checkpoint
go

/* THIS STORED PROCEDURE SHOW THE USED AND FREE SPACE FOR EACH DATABASE */

exec sp_showspace
go


Hope this helps Bernadette

 

[Guest_imported]

Yeah, thanks, I can find out the accurate number by a procedure, but I also want the EM to report this number correctly in the DB info window - see, it gives me a zero all the time, what started when I tried the recalculate button. Can anyone help?

-Mylo-

 

[Mylo]

Yeah, thanks, I can find out the accurate number by a procedure, but I also want the EM to report this number correctly in the DB info window - see, it gives me a zero all the time, what started when I tried the recalculate button. Can anyone help?

-Mylo-