One of the most frequent outages of a SQL Server on an unmonitored host is for sure due to running out of disk space. If you don’t keep an eye on your available disk space, expect things to start failing randomly one day I’d say.
Everything in the land of SQL depends on your unique workloads – Database (.mdf) files need to grow and database log (.ldf) files need to hold as much as it needs (depends 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 I’d be checking available disk space on the server when investigating an issue.
This blog post is to note down 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 your SQL Server host. I’m also including a note on how to get this information in PowerShell.
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);
The query above will return something that looks like this:
As simple as that!
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-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).