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
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,