Configure Alert in SQL Server for Severity Levels

Ever wondered what each error is associated with what severity level in SQL Server?

Every error which is logged in SQL Server Error log has a severity level assocoated with it. What level of severity needs to be taken on priority and what can be ignored can be found here in Microsoft Link

http://technet.microsoft.com/en-us/library/aa937483(v=sql.80).aspx

So what next after you get to know the severity level of alert.

I would prefer configuring alerts for the severity level which I feel is important and needs to be monitored.

Before configuring alerts, you need to have an operator configured to send emails. You can find details on how to configure Operator here: Configure Default Operator in SQL Server for sending alerts via Database Email

Once the Operator is configured, now let us configure a alert.

--Remove any alerts if existing, This you need not run if you dont want to as it removes all existing alerts.

USE msdb
GO

DECLARE
@myalert_id INT

DECLARE myCurRemAlert CURSOR FOR
SELECT  id FROM  sysalerts Order by id

OPEN myCurRemAlert FETCH NEXT FROM Cur_Remove_Alerts INTO @myalert_id

WHILE (@@fetch_status <> -1)
BEGIN
DELETE FROM msdb.dbo.sysnotifications WHERE alert_id = @myalert_id
DELETE FROM msdb.dbo.sysalerts WHERE id = @myalert_id

FETCH NEXT FROM myCurRemAlert INTO @myalert_id
END

CLOSE myCurRemAlert
DEALLOCATE myCurRemAlert

GO

--Creating Alert

DECLARE @myoperatorname VARCHAR(50)
SELECT  @myoperatorname = 'MyOperatorName'

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Sev. 17 Errors'))
 EXECUTE msdb.dbo.sp_delete_alert @name = N'Sev. 17 Errors'

BEGIN

--Adding Alert
 EXECUTE msdb.dbo.sp_add_alert
    @name = N'Sev level 17 Errors',
    @message_id = 0,
    @severity = 17,
    @enabled = 1,
    @delay_between_responses = 300,
    @include_event_description_in = 5,
    @category_name = N'[Uncategorized]' --You Can add any category here if you have one, else leave it default to Uncategorized

--Adding Notification

EXECUTE msdb.dbo.sp_add_notification
    @alert_name = N'Sev level 17 Errors',
    @operator_name = @myoperatorname,
    @notification_method = 1
END

 

The Same above code can be used to add alerts for different severity levels. Just replace the @severity value to any severity level and execute the code,