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

Running out of disk space is one of the most common issues that can happen with SQL Servers, particularly if the server is not being actively monitored. If you do not have a proper eye on your disk space usage, one day SQL Server will stop working as it has run out of available space. If you are in this situation you will need to delete data and shrink database files.

Get Disk Space Info in SQL Server

The SQL script below returns your SQL Server’s total disk space and available space for each volume attached:

-- 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);

This SQL script queries the sys.master_files and sys.dm_os_volume_stats system tables.

Get Server Disk Space using PowerShell

Now, as a bonus tip we’re looking at returning the same info with PowerShell. It’s as simple as running Get-Volume, which shows local volume information for your server. And if running on a remote server, use Enter-PSSession to connect to the target server first