SQL Server Disk Space Monitoring & Alert Script
This T-SQL script checks disk space used by SQL Server data and log files. If any drive has less than 30% free space, it sends a detailed HTML-formatted email alert to notify administrators. It helps prevent downtime or performance issues due to low disk availability.
🔧 What This Script Does
-
Captures Disk Space Stats using
sys.dm_os_volume_statsandsys.master_files. -
Excludes Specific Drives like
A:\,B:\,Z:\. -
Triggers HTML Email Alerts when free space on any drive is below 30%.
-
Uses Conditional Formatting in the email body:
-
🔴 Red for less than 10%
-
🟠 Orange for 10%–30%
-
💡 T-SQL Script
DECLARE @EmailBody NVARCHAR(MAX)
DECLARE @Subject NVARCHAR(100)
— Table variable to store results
DECLARE @DiskSpace TABLE (
Drive NVARCHAR(10),
Total_Size_GB INT,
Free_Space_GB INT,
Free_Space_Percentage DECIMAL(5,2)
)
— Get disk space details and exclude specific drives
INSERT INTO @DiskSpace
SELECT DISTINCT
vs.volume_mount_point AS Drive,
vs.total_bytes / 1024 / 1024 / 1024 AS Total_Size_GB,
vs.available_bytes / 1024 / 1024 / 1024 AS Free_Space_GB,
CAST((vs.available_bytes * 100.0 / vs.total_bytes) AS DECIMAL(5,2)) AS Free_Space_Percentage
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
WHERE vs.volume_mount_point NOT IN (‘A:\’,’B:\’,’Z:\’)
— Check for low disk space (less than 30% free)
IF EXISTS (SELECT 1 FROM @DiskSpace WHERE Free_Space_Percentage < 30)
BEGIN
SET @EmailBody =
‘<html>
<head>
<style>
table { border-collapse: collapse; width: 100%; }
th, td { border: 1px solid black; padding: 8px; text-align: left; }
th { background-color: #f2f2f2; }
</style>
</head>
<body>
<h3 style=”color: red;”>Warning! The following drives have low free space:</h3>
<table>
<tr>
<th>Drive</th>
<th>Total Size (GB)</th>
<th>Free Space (GB)</th>
<th>Free Space (%)</th>
</tr>’
SELECT @EmailBody = @EmailBody +
‘<tr style=”background-color:’ +
CASE
WHEN Free_Space_Percentage < 10 THEN ‘red’
WHEN Free_Space_Percentage < 30 THEN ‘orange’
ELSE ‘gray’
END + ‘;”>
<td>’ + Drive + ‘</td>
<td>’ + CAST(Total_Size_GB AS NVARCHAR) + ‘</td>
<td>’ + CAST(Free_Space_GB AS NVARCHAR) + ‘</td>
<td>’ + CAST(Free_Space_Percentage AS NVARCHAR) + ‘%</td>
</tr>’
FROM @DiskSpace WHERE Free_Space_Percentage < 30
SET @EmailBody = @EmailBody + ‘</table></body></html>’
SET @Subject = @@servername + ‘ – SQL Server Disk Space Alert: Low Free Space’
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLDBAActivity’,
@recipients = ‘dsafkljk@sda.com’,
@subject = @Subject,
@body = @EmailBody,
@body_format = ‘HTML’
END
📬 Sample Email Output
Here’s a preview of how the alert email looks in your inbox:
From: SQL DBA Alert <SQDBADMIN@sda.com>
Sent: Thursday, April 17, 2025 3:46 PM
To: dsafkljk@sda.com
Subject: FDB05 – SQL Server Disk Space Alert: Low Free Space
Warning! The following drives have low free space:
Drive Total Size (GB) Free Space (GB) Free Space (%)
D:\ 600 6 1.03%
I:\ 600 62 10.40%
L:\ 500 89 17.91%
🟠 Orange indicates warning, 🔴 red indicates critical.
📌 Tips for Usage
-
🔁 Schedule the script as a SQL Agent Job to run daily.
-
✏️ Customize the recipient list and profile name as per your environment.
-
🔒 Test in a non-prod environment before going live.
Nice article