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

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 5000 MB free and from there you can setup thresholds for when you want to be notified.