Select Page

Querying the Drive Space Utilization

Author: JP Chen | 3 min read | July 9, 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.
-- 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:

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

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Oracle RMAN Backup and Recovery with Restore Points

Oracle restore points are useful for benchmark testing. Find out how you can use Oracle’s Recovery Manager (RMAN) tool to create and use restore points.

Cindy Putnam | May 3, 2019

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS