Description
This alert will calculate free space for any drive containing SQL Server data files. It will return a list of drives with less than 5 GB free (which can be modified) and from there you can setup thresholds for when you want to be notified.
Alert Definition
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:
Alert Name: Monitor the Disk Free Space in MB
Execution Interval: 1 hour (or logical frequency for your environment)
Notification Text: The following disks are low on disk space.
SQL Statement:
SET NOCOUNT ON
DECLARE @DBname nvarchar(128),
@CMD1 nvarchar(200),
@Drive nvarchar(1),
@MB_Free int
IF OBJECT_ID('tempdb..#rdFreeSpace') IS NOT NULL
DROP TABLE #rdFreeSpace
IF OBJECT_ID('tempdb..#rdDataDrives') IS NOT NULL
DROP TABLE #rdDataDrives
CREATE TABLE #rdDataDrives (DB nvarchar(128), Drive nvarchar(1));
CREATE TABLE #rdFreeSpace (Drive char(1), MB_Free int);
INSERT INTO #rdFreeSpace EXEC xp_fixeddrives;
INSERT INTO #rdDataDrives
EXEC sp_MSforeachdb
@command1="use [?]
SELECT DB_NAME(), UPPER(LEFT(filename,1)) as DRIVE
FROM dbo.sysfiles"
-- always check the C: drive
INSERT INTO #rdDataDrives (Drive) VALUES ('C')
SELECT DISTINCT dd.Drive, fs.MB_Free
FROM #rdDataDrives dd, #rdFreeSpace fs
WHERE fs.Drive = dd.drive
AND fs.MB_Free < 5000
Execute Against: Monitored Instance
Units: MB Free
High Threshold: Min 0, Max 1024
Medium Threshold: Min 1024, Max 2048
