Tag: Database Mail using stored procedure in SQL Server 2005

  • 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.