I ran into some bad training that had convinced a client sys admin that the SQL Agent Alerts had the "greater than or equal to property."
As in, if I want to catch and notify an operator of all high severity errors 17 through 25, all I needed to do was set up an Alert for Severity 17. This is false.
Here's a quick proof:
--This query will not run in a batchfor obvious reasons. The high-severity
error messages will kill the batch.
--Run each statement manually andobserve the results.
EXEC msdb.dbo.sp_add_alert @name=N'020', @delay_between_responses=0, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000' --This query will return date and time0 | 0. The new alert exists.
select name, last_occurrence_date, last_occurrence_timefrom msdb.dbo.sysalerts where name = '020'
RAISERROR (N'testingonly. This is message %s %d.'
,-- Message text.
N'number', -- First argument. 5) WITH LOG; -- Second argument. --This query will still return date andtime 0 | 0. Alert for Severity 20 has
NOT been triggered by a Severity 21 error.
select name, last_occurrence_date, last_occurrence_timefrom msdb.dbo.sysalerts where name = '020'
RAISERROR (N'testingonly. This is message %s %d.'
,-- Message text.
N'number', -- First argument. 5) WITH LOG; -- Second argument. --This query will return a valid dateand time. Alert for Severity 20 has been
triggered by a Severity 20 error.
select name, last_occurrence_date, last_occurrence_timefrom msdb.dbo.sysalerts where name = '020'
As you run the above script statement-by-statement (NOT in a batch), you'll see the occurrence data trigger only for an error with the severity EQUALS the defined alert state. In this case, the Severity 21 error did NOT trigger the Severity 20 alert.
Here's a basic script from my toolbox to set up Alerts for all significant error severities, to notify your operator via email with details. In this script, the operator is called "DBAs", and ideally would be a distribution group for all active members of the Database Administrator role in your IT department.
Make sure that database mail is setup, that SQL Agent is configured to use the appropriate Database Mail profile, and that the failsafe operator is enabled to email your DBA team.
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 17', @delay_between_responses=600, @include_event_description_in=1 EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 17', @operator_name=N'DBAs', @notification_method= 1
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 18', @delay_between_responses=600, @include_event_description_in=1 EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 18', @operator_name=N'DBAs', @notification_method= 1
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 19', @delay_between_responses=600, @include_event_description_in=1 EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 19', @operator_name=N'DBAs', @notification_method= 1
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 20', @delay_between_responses=600, @include_event_description_in=1 EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 20', @operator_name=N'DBAs', @notification_method= 1
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 21', @delay_between_responses=600, @include_event_description_in=1 EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 21', @operator_name=N'DBAs', @notification_method= 1
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 22', @delay_between_responses=600, @include_event_description_in=1 EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 22', @operator_name=N'DBAs', @notification_method= 1
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 23', @delay_between_responses=600, @include_event_description_in=1 EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 23', @operator_name=N'DBAs', @notification_method= 1
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 24', @delay_between_responses=600, @include_event_description_in=1 EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 24', @operator_name=N'DBAs', @notification_method= 1
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 25', @delay_between_responses=600, @include_event_description_in=1 EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 25', @operator_name=N'DBAs', @notification_method= 1
GO
More info:
Error severity info: http://msdn.microsoft.com/en-us/library/ms164086.aspx
