SQL Server Drive Free Space Monitor (T-SQL + PowerShell)

Here’s a simple “set it and forget it” script I keep in my toolbox. It checks Windows drive space on the SQL Server host and emails an HTML table when any drive falls below 10% free. It can run hourly as a SQL Agent job.

I didn’t originally write this. I combined notes from old scripts and kept the logic intact so you can copy and paste it.

What it does

• Enables xp_cmdshell temporarily and calls PowerShell to read Win32_Volume.
• Loads capacity and free space into a temp table.
• If any volume is under 10% free, it sends a formatted email.
• Designed to run on the local server, no linked servers required.

Prerequisites

• Database Mail configured with a working profile.
• PowerShell available on the SQL Server host.
• The SQL Server service account must be able to query WMI on the local machine.
• You’re OK using xp_cmdshell. If not, run it inside a locked-down environment or use an alternative.

How to use it

1. Create or pick a utility database.
2. Replace the placeholders for database name, DB Mail profile, and recipient.
3. Run the script to test.
4. Create a SQL Agent job to run it every hour (or whatever you prefer).

---

Copy-paste script (placeholders included)

USE [YourUtilityDB]
GO
 
/****** 
This uses powershell and Tsql
Puts data in a temp table and can be run hourly in a SQL Agent job to check space at the threshold you need. (Currently set at 10%)
Sends an email out when hits the threshold set
******/
sp_configure 'show advanced options', 1
go
Reconfigure with override
go
sp_configure 'xp_cmdshell', 1
go
 
Reconfigure with override
go
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
Declare @svrName varchar(255)
Declare @sql varchar(400)
 
--by default it will take the current server name, we can the set the server name as well
SET @svrName = @@SERVERNAME
SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') +
 ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
--creating a temporary table
CREATE TABLE #output
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
INSERT #output
EXEC xp_cmdshell @sql
 
CREATE Table ##DriveSpace
           (ServerName sysname,
           [PhysicalName] sysname,
           [Capacity(GB)] nvarchar(20),
           [Drive FreeSpace(GB)]  nvarchar(20))
 
INSERT INTO ##DriveSpace
           ([ServerName]
           ,[PhysicalName]
           ,[Capacity(GB)]
           ,[Drive FreeSpace(GB)])
    SELECT @@SERVERNAME
           ,rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as PhysicalName
          ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
          (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
          ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
          (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'Drive freespace(GB)'
    FROM #output
    WHERE line like '[A-Z][:]%'
    order by PhysicalName
 
DROP TABLE #output
 
SELECT * FROM ##DriveSpace
WHERE (Cast([Drive FreeSpace(GB)] as float)/Cast([Capacity(GB)] as float)) < .10 
 
IF  EXISTS ( SELECT * FROM ##DriveSpace WHERE (Cast([Drive FreeSpace(GB)] as float)/Cast([Capacity(GB)] as float)) < .10 ) 
 
BEGIN
 
            DECLARE @tableHTML NVARCHAR(MAX); 
            SET @tableHTML = '


<body style="font-family:Sans-serif;">
<table style="width: 100%;border-collapse:collapse;border:1px solid;">
	<tr style="background-color:#404040;color:#FFFFFF;">
		<th>Drive Free Space under 10%</th>
	</tr>
	</table>
<table style="width: 100%;border-collapse:collapse;border:1px solid;">
	<tr style="text-align:left;background-color:#CC0000;color:#FFFFFF;">
		<td style="border: 1px solid black">ServerName</td>
		<td style="border: 1px solid black">Drive</td>
		<td style="border: 1px solid black">Capacity(GB)</td>
		<td style="border: 1px solid black">Drive FreeSpace(GB)</td>
	    </tr>' +
    CAST ( ( SELECT td = ServerName,     '',
                    td = [PhysicalName], '',
                    td = [Capacity(GB)], '',
                    td = [Drive FreeSpace(GB)], ''
              FROM  ##DriveSpace  
              WHERE (Cast([Drive FreeSpace(GB)] as float)/Cast([Capacity(GB)] as float)) < .10 
              FOR XML PATH('tr'), TYPE 
              ) AS NVARCHAR(MAX) ) +
              N'</table>' ;
 
    SET @tableHTML = replace(replace(@tableHTML, '&lt;', '<'), '&gt;', '>')+ '</table>'
     
            EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = 'YourDatabaseMailProfile',
            @recipients   = 'ops@example.com',
            @subject      = 'Drive Free Space under 10%',
            @body         = @tableHTML,
            @body_format  = 'HTML' ;
END
 
DROP TABLE ##DriveSpace  
 
go
 
sp_configure 'show advanced options', 0
go
Reconfigure with override
go

Notes and tweaks

• Threshold: it’s set at 10% in two places. If you want 15%, change .10 to .15 in both the SELECT and the IF EXISTS.
• Security: xp_cmdshell is enabled here and left on as the original script does. If you prefer to turn it off after the run, add this as a separate step in your Agent job:

EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE WITH OVERRIDE;

• Agent job: create a job with one T-SQL step that runs the script and schedule it hourly.
• HTML styling: adjust colors and fonts in the HTML string if you want a different look.

Summary

This script checks Windows drive space from SQL Server using PowerShell/WMI and loads the results into a temp table. If any drive drops below 10% free, it emails a simple HTML report so you can act before things break.

• Runs on a schedule as a SQL Agent job.
• Uses xp_cmdshell to call PowerShell.
• Threshold is easy to change (look for .10).
• Requires Database Mail (profile name matches the one in the script).

If you’re not comfortable leaving xp_cmdshell enabled, run the monitor in one step and add a second job step to disable it after each run.