Description

This custom Ignite alert will calculate free space for any drive containing SQL Server data files. It will return a list of drives with less than 5000 MB free and from there you can setup thresholds for when you want to be notified.

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

Ignite alert definition