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.
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:
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.
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.