Show Available Disk Space in SQL Server

This blog post is to share a script that you can run in SQL Server (versions 2008 R2 and above) which shows the available disk space for all local volumes on a SQL Server host. I’m also including a quick note on how to get this information with remote PowerShell.

Running out of disk space is one of the most common issues that happens on unmonitored database servers. If you do not monitor your disk space usage of a SQL Server, then things will randomly come to a halt one day. Your drives are full and your SQL Server will not accept any more updates until you get more space.

Everything performance related in SQL Server depends on your unique workloads, queries, application, platform & environment. Database files need to grow and database log files need to hold as much as it needs (depending on Recovery Models). Whether it’s a very slow ever-increasing table that bites you, or it might be a user query that bloats log files, when an unmonitored SQL Server is having issues checking available disk space on the host is one of the first steps you should take when you connect & bein diagnosing.

As mentioned at the top of this post, this blog post is a how-to on getting your available disk space a SQL Server host, using a TSQL script and PowerShell:
# Get Disk Space Info in SQL Server
# Get Disk Space Info PowerShell

Get Disk Space Info in SQL Server

The TSQL script below queries sys.master_files and sys.dm_os_volume_stats SQL Server internal system metadata views.

and will return the disk space and available space within each volume attached to the SQL host.

-- get available disk space sql server
SELECT DISTINCT
    vs.volume_mount_point,
    vs.file_system_type,
    vs.logical_volume_name,
    CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)],
    CONVERT(DECIMAL(18,2), vs.available_bytes/1073741824.0) AS [Available Size (GB)],
    CONVERT(DECIMAL(18,2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %]
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
ORDER BY vs.volume_mount_point OPTION (RECOMPILE);

SQL Server Disk Space Query Result — *SQL disk space query output*

Now get this info sent to your email via Database Mail if the availability of the SQL Server means anything to you. If it’s business-critical that no new data stops being inserted into an online database, then you’d already have a decent 3rd party monitoring tool in place.

Get Disk Space PowerShell

Get-Volume is the command to show local volume information for your server. If running on a remote server, use Enter-PSSession to connect to the target server first (requires Local Admin permissions).

SQL DBA Blog