Select Page

Querying the Drive Space Utilization

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

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Tips for Upgrading From SQL 2008 to 2012 or 2014

It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.

Andy McDermid | April 8, 2015

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