Determining free disk space with TSQL
Working on a new report for the SQL Server Health reports, I needed to display the amount of free disk space on a SQL Server.
EXEC MASTER..Xp_fixeddrives;
Which was useful if I just wanted to look, but I needed to use the results in a query, and I didn’t want to put the results into a temp table, so here is how I decided to do it, using table variables.
DECLARE @disk_space TABLE(drive CHAR(1) NOT NULL, free INTEGER NOT NULL)
INSERT INTO @disk_space
EXEC MASTER..Xp_fixeddrives;
SELECT drive,
CAST(free* 10 / 1024 AS FLOAT) / 10 AS gbfree
FROM @disk_space
ORDER BY gbfree ASC;
With this, I am now about to query the @disk_space table variable and join it with other tables in the database.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Not sure if you care, but this does not work with mount points. It gives the space on the head, which for us is standard 10GB. But we have multiple mount points under the drive letters and your query does not show the space of the mount points.
Good point. I probably should have pointed that out in the original post.
xp_fixeddrives does not display information for mount points, only normal fixed drives.
This is a known problem with xp_fixeddrives, a good alternative is to use powershell and Get-WmiObject to get the actual disk space on mount points.
I hope this helps clarify things.
-Steve Stedman
Bellingham WA