Email alert on disk space example

--create temp table for results CREATE TABLE #freespace ( drive CHAR(1) ,mb_free INT ) --insert drive data into temp table INSERT INTO #freespace EXEC sys.xp_fixeddrives DECLARE @drive CHAR(1) DECLARE @subject VARCHAR(100) DECLARE @profile_name VARCHAR(25) DECLARE @body VARCHAR(200) DECLARE @gb_free INT DECLARE @recipients VARCHAR(50) --you can specify whatever drive leter DECLARE WorkStationCursor CURSOR FAST_FORWARD FOR SELECT [drive] ,[mb_free] FROM #freespace ORDER BY [drive] DESC OPEN WorkStationCursor FETCH NEXT FROM WorkStationCursor INTO @drive ,@gb_free WHILE @@FETCH_STATUS = 0 BEGIN SELECT @gb_free = (@gb_free / 1024) -- from #freespace where drive = 'E' IF (@gb_free < 20) BEGIN PRINT @drive SET @profile_name = 'chmailserver' SET @recipients = 'sysadmins@crouse.org' SET @subject = '(TEST) ALERT!! ' + @drive + ' drive is BELOW 20 GB' SET @body = 'Please check the ' + @drive + ' drive! It has fallen below 20 GB of free space. There is currently ' + CONVERT(VARCHAR, @gb_free) + ' GB of free space left on the ' + @drive + ' drive.' EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name ,@body = @body ,@subject = @subject ,@recipients = @recipients END FETCH NEXT FROM WorkStationCursor INTO @drive ,@gb_free END CLOSE WorkStationCursor DEALLOCATE WorkStationCursor --you can specify whatever number you want. I put 20GB. /*if (@gb_free < 100) BEGIN SET @profile_name = 'chmailserver' SET @recipients = 'sysadmins@crouse.org;3156575099@vtext.com' SET @subject = 'ALERT!! E: drive is BELOW 100 GB' SET @body = 'Please check the E: drive! It has fallen below 20 GB of free space. There is currently ' + CONVERT(varchar,@gb_free) + ' GB of free space left on the E drive.' exec msdb.dbo.sp_send_dbmail @profile_name = @profile_name, @body = @body, @subject = @subject, @recipients = @recipients END */ --drop temp table DROP TABLE #freespace; GO