Querying the Drive Space Utilization

How can you query the disk drives space utilization to show all the drives, total capacity, free space, and percentage free for the drives that contain the SQL Server databases data and log files? In addition, how can you be alerted when any one of the disk drives containing the database data or log files is running low on disk space?

You may have noticed that when you view the disk drive details in the server, you see the hard drives, their labels, total size, and free space. But, it doesn’t show you the percentage free in number, as shown in the following screen-shot. You want to see the percentage free so that you can customize the query to send out alerts when a certain threshold is reached. Can this be done?

Yes it can! The sys.dm_os_volume_stats() Dynamic Management Function (DMF) to your rescue. It returns the information about the operating system volume(s) the databases files are stored. One caveat is that it works only for SQL Server 2008 R2 SP1 and later versions. If your SQL Server instances are all running the latest versions such as SQL Server 2012 or 2014, this shouldn’t be an issue. The following are the required functions, conversions, and formulas you will need in addition to the sys.dm_os_volume_stats() DMF and brief explanations of each:

• DISTINCT: Returns only the distinct values. Suppress the duplicates from the result set.
• Converting bytes to megabytes: The sys.dm_os_volume_state() DMF returns the drive space details in bytes. To convert them to megabytes, we will need to divide the data columns that are in bytes by 1048576.
• CAST(): Converts an expression of one data type to another. Here, we will use the CAST() function to convert the required data columns to data type of decimal and round the decimal points to two.
• Percentage of Free Space: Free Space in MB multiplied by 100 and then divided by Total Space in MB.
• CROSS APPLY operator: Returns only those rows from the left table expression if it matches with the right table expression.

-- Querying the Drive Space Utilization
-- ****************************************************************************
-- Copyright © 2015 by JP Chen of DatAvail Corporation
-- This script is free for non-commercial purposes with no warranties.
-- ****************************************************************************

SELECT DISTINCT
vs.volume_mount_point [Drive],
CAST(vs.total_bytes / 1048576 as decimal(12,2)) [TotalMBs],
CAST(vs.available_bytes / 1048576 as decimal(12,2)) [FreeSpaceMBs],
CAST((CAST(vs.available_bytes / 1048576 as decimal(12,2)) * 100) / (CAST(vs.total_bytes / 1048576 as decimal(12,2))) as decimal(12,2)) [% Free]
FROM
sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) vs

Example output:

You can further customize the query to alert you and team when the percentage of free space reach a set threshold. For more details on how to enable Database Mail and sending out alerts, see my white paper “Hands-On Guide to Automatic Notifications and Scheduled Reports with Database Mail”.