Understanding the drive space usage on your SQL Server is essential for maintaining optimal performance and preventing storage-related issues. SQL Server offers several ways to monitor drive usage directly from a query. In this post, we’ll explore three different approaches, each with its unique use case and level of detail.

1. Querying Drive Space Using sys.dm_os_volume_stats

The sys.dm_os_volume_stats dynamic management function is a reliable way to retrieve detailed drive usage statistics for volumes associated with SQL Server database files. This method is particularly useful for DBAs who want a comprehensive view of drive usage. One caveat though: sys.dm_os_volume_status is only going to return information for drives associated with SQL Server database files which, for most SQL Servers, isn’t going to include your operating system drive.

Here’s the query:

select distinct
  a.volume_mount_point
, convert(decimal(20,2), (((a.total_bytes / 1024.0) / 1024.0) / 1024.0)) as [DriveTotalSpace_GB]
, convert(decimal(20,2), (((a.available_bytes / 1024.0) / 1024.0) / 1024.0)) as [DriveAvailableSpace_GB]
, convert(decimal(20,2), ((((a.total_bytes - a.available_bytes) / 1024.0) / 1024.0) / 1024.0)) as [DriveUsedSpace_GB]
, convert(decimal(20,2), ((((a.available_bytes / 1024.0) / 1024.0) / 1024.0) / (((a.total_bytes / 1024.0) / 1024.0) / 1024.0)) * 100) as [DriveAvailablePercentage]
, convert(decimal(20,2), (((((a.total_bytes - a.available_bytes) / 1024.0) / 1024.0) / 1024.0) / (((a.total_bytes / 1024.0) / 1024.0) / 1024.0)) * 100) as [DriveUsedPercentage]
from sys.master_files as [b]
cross apply sys.dm_os_volume_stats(b.database_id,b.[file_id]) as [a];

Output: Provides detailed metrics, including total space, available space, used space, and percentages

Why Use This Query?

• Provides detailed metrics, including total space, available space, used space, and percentages.
• Helps monitor drive usage specifically for volumes where SQL Server database files are located.
• Useful for regular storage audits and performance troubleshooting.

Why NOT Use This Query?

• Does not display usage statistics for drives that are not associated with a SQL Server database

2. Using xp_fixeddrives to retrieve drive free space

The xp_fixeddrives extended stored procedure is a simpler option for quickly checking free space on all drives accessible to SQL Server. While it doesn’t provide as much detail as sys.dm_os_volume_stats, it’s fast and easy to use.

Here’s the query:

exec xp_fixeddrives;

Output:

The result shows each drive letter along with the free space (in MB) available on that drive.

Why Use This Query?

• Quick and straightforward for a high-level view of available space.
• Ideal for basic monitoring or initial troubleshooting.
• No need to join multiple tables or write complex calculations.

Why NOT use this query?

• Does not provide total drive size or percentages.
• Limited to drives accessible to SQL Server.

3. Using sys.dm_os_enumerate_fixed_drives

sys.dm_os_enumerage_fixed_drives is DMV enumerates fixed mounted volumes and returns information about them.

SELECT *
    ,convert(decimal(20,2), (((free_space_in_bytes / 1024.0) / 1024.0) / 1024.0)) 'Free Space (GB)'
FROM sys.dm_os_enumerate_fixed_drives;

Why Use This Query?

• Quick and straightforward for a high-level view of available space.
• Ideal for basic monitoring or initial troubleshooting.
• No need to join multiple tables or write complex calculations.

Why NOT use this query?

• Although sys.dm_os_enumerate_fixed_drives returns more information than xp_fixeddrives, it still lacks in the sense that it is not going to return total drive size so that you can calculate used percentage.

Conclusion:

At the end of the day these are all quick and easy ways to get drive info from SQL Server… however they all lack in one form or another. There are a plethora of other utilities, including some simple PowerShell commands, that can give you a full picture of your drive usage on your SQL Servers.