Disk Space Alerts using SQL Server 2005

Disk Space Alerts using SQL Server 2005

http://sqldbpool.com/2008/04/16/disk-space-alerts-using-sql-server-2005/

Please visit http://sqldbpool.blogspot.com/

Step 1: Create the database mail profile account using SQL Server Management Studio. Give the profile name to “FreeSpaceAlertMails”

Step2: Create the below procedure in master database which will check the disk space.

CREATE PROCEDURE sendAlertMails
AS
BEGIN
SET NOCOUNT ON

DECLARE @availableSpace AS FLOAT
DECLARE @alertMessage AS Varchar(4000)

CREATE TABLE #tbldiskSpace
(
driveName VARCHAR(3),
freeSpace FLOAT
)

INSERT INTO #tbldiskSpace EXEC master..XP_FixedDrives
SELECT @availableSpace = ROUND((freeSpace)/1024,1)
FROM #tbldiskSpace
WHERE driveName = ‘E’

SET @alertMessage = ‘(host:jshah.sqldbpool.com)E:\ Disk Space Critical.Free Space Available on E:\ Drive is ‘ + CAST(@availableSpace AS VARCHAR) + ‘GB’

IF @availableSpace < 10
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘FreeSpaceAlertMails’,
@recipients = ‘jshah143@gmail.com’,
@body = @alertMessage,
@importance = ‘High’,
@subject = ‘host:jshah.sqldbpool.com Disk Critical E Drive’;
END

DROP TABLE #tbldiskSpace
END

Step 3: Create the job which will execute the above procedure at every 2 hours interval.