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.

13 comments » Write a comment

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

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

      • 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

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

  4. 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_DRIVES
      
  5. Hi 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 )

Leave a Reply

Required fields are marked *.