Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Using T-SQL to Check Database Memory
Microsoft’s Gert Drapers shows how to use T-SQL to check a database’s memory usage.
Microsoft's Sql Server Development Team
November 1, 2005
1 Min Read
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 Enterprise Manager uses SQL-DMO to retrieve the information.
About the Author(s)
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
.jpg?width=700&auto=webp&quality=80&disable=upscale)