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