You are currently viewing An alternative to xp_fixeddrives
Microsoft SQL Server

An alternative to xp_fixeddrives

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.

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

This Post Has 15 Comments

  1. Noob Question

    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.

    1. Tomas Lind

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

    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.

    1. Tomas Lind

      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

      1. Baja

        Tomas, thank you so much for a fast reply, that did the trick.

        Best regards.

      2. Manuel

        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

          1. Manuel

            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.

  3. Channdeep Singh

    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.

    1. Tomas Lind

      Hi, on a SQL Server version prior to 2008R2 you have to use xp_fixeddrives instead. Only returns free space however.

      Regards
      Tomas

  4. Samuel

    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?

    1. Tomas Lind

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

    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 )

  6. Gus

    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

  7. Mark Proctor

    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.

Leave a Reply