An alternative to xp_fixeddrives

Microsoft SQL Server

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
xp_fixeddrives Results

xp_fixeddrives Results

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:

sys.dm_os_volume_stats Drives Free Space

sys.dm_os_volume_stats Drives Free Space

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.

14 comments » Write a comment

Leave a Reply

Required fields are marked *.