Create Severity Alerts

Sometimes it’s the simple things that are the easiest to forget, such as setting up automatic, server-level error alerting. It only takes a minute to create an operator (an individual, or, better yet, a group!) to receive notifications, define various alert events, and create a notification that will inform the operator when the alert event occurs.

The following script will do just that for database engine error severities 16 through 25.

-- Set context to the msdb database
USE msdb;
GO

-- Declare variables
--   @strOperator = operator name
--   @strOperatorEmail = operator's email address
--   @intSeverity = the lowest severity level to alert on
--   @intSeverityMax = the highest severity level to alert on
--   @strAlertName = a dynamic variable with which to name alerts
DECLARE
    @strOperator VARCHAR(50) = N'DBA'
    , @strOperatorEmail VARCHAR(50) = N'dba_group@yourdomain.com'
    , @intSeverity TINYINT = 16
    , @intSeverityMax TINYINT = 25
    , @strAlertName VARCHAR(50)
;

-- If the operator we want to create already exists, drop it
IF EXISTS (
    SELECT name
    FROM msdb.dbo.sysoperators
    WHERE name = @strOperator)
EXECUTE msdb.dbo.sp_delete_operator
    @name = @strOperator
;

-- Create a new operator
--   Best practice is to use a distribution list, not an individual
EXECUTE msdb.dbo.sp_add_operator
    @name = @strOperator
    , @email_address = @strOperatorEmail
;

-- Loop through the desired severity levels
WHILE @intSeverity <= @intSeverityMax
BEGIN

    -- Set the alert name
    SET @strAlertName = N'Severity 0' + CONVERT(CHAR(2), @intSeverity)
    ;

    -- If the alert we want to create already exists, drop it
    IF EXISTS (
        SELECT name
        FROM msdb.dbo.sysalerts
        WHERE name = @strAlertName)
    EXECUTE msdb.dbo.sp_delete_alert
        @name = @strAlertName
    ;

    -- Create the alert
    EXECUTE msdb.dbo.sp_add_alert
        @name = @strAlertName
        , @severity = @intSeverity
        , @delay_between_responses = 60 -- seconds
        , @include_event_description_in = 1 -- email only
    ;

    -- Add notification to the alert
    EXECUTE msdb.dbo.sp_add_notification
        @alert_name = @strAlertName
        , @operator_name = @strOperator
        , @notification_method = 1 -- send email only
    ;

    -- Increment the severity
    SET @intSeverity += 1
    ;

END

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: