Querying the Drive Space Utilization

By | In SQL Server | July 09th, 2015

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?

drv_util

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.

Example output:

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

Contact Us
JP Chen
Practice Leader of SQL Server Services
JP has over 13 years of experience in the IT industry specializing in web development, data analysis, and database administration. He has worked on supporting enterprise level corporations in the financial, retailing, pharmaceutical, aerospace, fashion, and education industries as a DBA and Tech Lead. He has initiated, designed, and developed comprehensive solutions on database documentation, monitoring and alerting, stabilization, and continuous improvements with performance monitoring and tuning. He enjoys sharing his new findings, knowledge, and practical hands-on experiences on SQL Server via his two blogs: www.handsonsqlserver.com and www.handsontsql.com.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

1 thought on “Querying the Drive Space Utilization”
  1. HI JP,

    The script is perfectly working for the drive space information where the database files are located in the server.

    And its not giving the drive information about other drives which are exists on the server (ex. I use to store backup info in other drive ie G drive and I not getting these drive info)

    Thanks