Advertisement
When I use the TaskPad in Enterprise Manager to check a database's allocated, used, and free space (as reported on the General tab), the values I see don't match the values I get when I use the sp_HelpDB and sp_Spaceused stored procedures. For example, when I checked one of my databases today, I got the following information:
- In TaskPad, Enterprise Manager reported that the database had 211549.75MB of allocated space, 110294.44MB of used space, and 101255.31MB of free space.
- For the same database, the sp_Spaceused stored procedure told me I had 212113.50MB of allocated space and 19541.14MB of unallocated space.
- The sp_helpDB stored procedure reported that I had an MDF file size of 211549.75MB.
Can I use T-SQL to return the same information that I see when I use the TaskPad?
To generate the information on the TaskPad, Enterprise Manager uses a combination of the following four queries.
EXEC sp_spaceused
SELECT fileid, name, filename, size, growth, status, maxsize
FROM dbo.sysfiles WHERE (status & 0x40) <>0
DBCC sqlperf(logspace)
DBCC showfilestats
SELECT fileid, name, filename, size, growth, status, maxsize
FROM dbo.sysfiles WHERE (status & 0x40) <>0
DBCC sqlperf(logspace)
DBCC showfilestats
Enterprise Manager uses SQL-DMO to retrieve the information.
