Select Page

Querying the Drive Space Utilization

Author: JP Chen | | 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:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

MongoDB Best Practices: Design, Deployment & More

This post provides a rundown of best practices to use when running MongoDB.

Esayas Aloto | February 28, 2017

How To Fix Log Shipping Failure Errors In SQL Server

Learn how to fix common Log Shipping Failure errors in SQL Server. Includes step-by-step instructions, screenshots, and software script.

Eric Russo | August 10, 2016

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