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 |
|
Blin
Starting Member
36 Posts |
 Posted - 2004-11-05 : 17:32:38
|
| In Enterprise Manager, I can go to View and check Taskpad, and it will list used and free space sizes for data and log of a database (used in gray and free in blue).I'd like to know what commands I may use to get the same info (used space and free space) in Query Analyzer. The followings are what I've tried:(1)select size*8000/1024 [Size (KB)], name [Logical Name], filename [Physical File Name]from sysfilesBut this gives me 'allocated' data and log file sizes, which are the same as *.mdf and *.ldf.(2)sp_spaceused @updateusage = 'TRUE'This lists db size (allocated), reserved, data, index, and unused sizes. The db size is the same as size in EM and same as that of sysfiles, but the unused is not the same as free space in EM.(3)dbcc sqlperf(logspace)This is good command and gives me log info that I needed.I also need to insert those size info into a user table for db growht monitoring.Thanks for your input. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-05 : 18:13:00
|
| Take the total reserved amount from sp_spaceused and subtract it from the total file size from sysfiles. The difference should be the total unused space in the database. |
 |
|
|
|
|
|
Database size