Want to know the disk space available on your database server from T-SQL? This can be done in many ways, here I’ll present two of them. The first way is by using xp_fixeddrives. With the release of SQL Server 2008R2 there was another (better) way to get the same information and more by using the system function sys.dm_os_volume_stats.
xp_fixeddrives
This is an undocumented system stored procedure that has been around since SQL Server 2000. It simply returns the drive letter and the amount of free space on each drive in MB:
EXEC xp_fixeddrives
To use the results in any T-SQL logic, you need to store the results in a temporary table like this:
IF EXISTS (SELECT OBJECT_ID('tempdb..#TMP_DRIVES'))
DROP TABLE #TMP_DRIVES
CREATE TABLE #TMP_DRIVES
(
DRIVE CHAR(1) NOT NULL,
MBFREE INT NOT NULL
)
INSERT INTO #TMP_DRIVES
EXEC xp_fixeddrives
The obvious disadvantage with xp_fixeddrives is that it doesn’t return the total size of the disks, only the free space.
As from SQL Server 2008R2 there is a new method to get disk size information by using the function sys.dm_os_volume_stats.
sys.dm_os_volume_stats
The function sys.dm_os_volume_stats contains information on each file in each database in the instance. That’s more detail than we need for our purpose, so we need to aggregate that information to get the numbers for each drive:
SELECT DISTINCT SUBSTRING(volume_mount_point, 1, 1) AS volume_mount_point ,total_bytes/1024/1024 AS total_MB ,available_bytes/1024/1024 AS available_MB FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);
As you can see, this also returns information on total space on each drive:
Since this method uses no system stored procedures, there is no need for temporary tables anymore. Although, for convenience you may want them anyway 🙂
Also note that sys.dm_os_volume_stats only returns information from drives used for storage of database files. As you can see from the examples above xp_fixeddrives returns free space on my C drive, but sys.dm_os_volume_stats does not. Normally that’s not a problem since I am only concerned with the space available for the databases.
Shouldn’t it be GB since bytes / 1024 / 1024 ? Otherwise you would only have 62K free on your drive.
Minor issue. I used you code to solve my need.
Thank you.
Thanks for the feedback!
You’re right, almost 🙂 “total_bytes/1024/1024” is MB. “total_bytes/1024/1024/1024” is GB.
I’ll change the column name in script.
Thanks for the code, didn’t know that.
Since I’m still a beginner, is it possible to add a column that shows percentage of free space per mount point?
Thank you.
Try this:
SELECT DISTINCT SUBSTRING(volume_mount_point, 1, 1) AS volume_mount_point ,total_bytes/1024/1024 AS total_MB ,available_bytes/1024/1024 AS available_MB ,ISNULL(ROUND(available_bytes / CAST(NULLIF(total_bytes, 0) AS FLOAT) * 100, 2), 0) as percent_available FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);/Tomas
Tomas, thank you so much for a fast reply, that did the trick.
Best regards.
Was that code snippet written using ssms? If so how did you theme it with those colors? I’m looking for a way to get a dark look for ssms. Staring at all that white background for hours kills the eyes. Would like to know what you did. Thanks
Hi Manuel, the code is not from SSMS, it is from a WordPress plugin called SyntaxHighlighter Evolved.
Ah ok thanks. I’m still searching for ways to make ssms look like that to avoid eyeball death by the end of a workday.
Dear Sir- This is not working on sql2005 as sys.dm_os_volume_stats is not available in 2005. I have tried finding alternative but no lock. Could you check as well. Thanks and regards.
Hi, on a SQL Server version prior to 2008R2 you have to use xp_fixeddrives instead. Only returns free space however.
Regards
Tomas
I have different servers and I want to monitor the disk space available on all servers that inlcudes
DriveNames
Total Space
Available Space
Available SpacePreviousday
UsedSpace
where that UsedSpace obtained is (Available SpacePreviousday – Available Space).
Could you please help me with this?
Hi, if you store the date for each measurement, you can use the LAG function to get the value from the previous measurement. Something like this:
IF EXISTS (SELECT OBJECT_ID('tempdb..#TMP_DRIVES')) DROP TABLE #TMP_DRIVES CREATE TABLE #TMP_DRIVES ( ID INT NOT NULL, MEASUREDATE DATE NOT NULL, DRIVE CHAR(1) NOT NULL, MBFREE INT NOT NULL ) INSERT #TMP_DRIVES VALUES (1, '2016-09-26', 'C', 100), (2, '2016-09-27', 'C', 90), (3, '2016-09-28', 'C', 85) SELECT ID, MEASUREDATE, DRIVE, MBFREE, LAG(MBFREE, 1, MBFREE) OVER (PARTITION BY DRIVE ORDER BY ID) AS MBFREEYESTERDAY, MBFREE - LAG(MBFREE, 1, MBFREE) OVER (PARTITION BY DRIVE ORDER BY ID) AS USEDSPACE FROM #TMP_DRIVESHi Tomas,
This script working perfectly for the drive where database related file stored.
Now I would like to add get the information from one more drive where I use to store database backup.
Current script is not working for to gather all the drives which are available from the server . It would be great if you can suggest how to monitor all the drive .(At least the files stored were database related + backup related )
Hi,
We store the backup Data and Log files on the E: drive.
How could I add this drive to the above query?
Thanks,
Gus
From what I can tell, if you have no DB files on an empty volume yet, then dm_os_volume_stats will not show you the empty space, so not an ideal solution.