SQL Agent Alerts - Severity Condition is NOT "greater than or equal to"

,

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:

USE [msdb]

GO

--This query will not run in a batch

for obvious reasons.  The high-severity

error messages will kill the batch.

--Run each statement manually and

observe the results.

EXEC msdb.dbo.sp_add_alert @name=N'020',

              @message_id=0,

              @severity=20,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=1,

              @job_id=N'00000000-0000-0000-0000-000000000000'

GO

--This query will return date and time

0 | 0.  The new alert exists.

select name, last_occurrence_date, last_occurrence_time

from msdb.dbo.sysalerts where name = '020'

GO

RAISERROR (N'testingonly. 

This is message %s %d.',

-- Message text.

           21, -- Severity,

           1, -- State,

           N'number', -- First argument.

           5) WITH LOG; -- Second argument.

GO

--This query will still return date and

time 0 | 0.  Alert for Severity 20 has

NOT been triggered by a Severity 21 error.

select name, last_occurrence_date, last_occurrence_time

from msdb.dbo.sysalerts where name = '020'

GO

RAISERROR (N'testingonly. 

This is message %s %d.',

-- Message text.

           20, -- Severity,

           1, -- State,

           N'number', -- First argument.

           5) WITH LOG; -- Second argument.

GO

--This query will return a valid date

and time.  Alert for Severity 20 has been

triggered by a Severity 20 error.

select name, last_occurrence_date, last_occurrence_time

from msdb.dbo.sysalerts where name = '020'

GO

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.  

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 17',

              @message_id=0,

              @severity=17,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 17', @operator_name=N'DBAs', @notification_method

= 1

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 18',

              @message_id=0,

              @severity=18,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 18', @operator_name=N'DBAs', @notification_method

= 1

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 19',

              @message_id=0,

              @severity=19,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 19', @operator_name=N'DBAs', @notification_method

= 1

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 20',

              @message_id=0,

              @severity=20,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 20', @operator_name=N'DBAs', @notification_method

= 1

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 21',

              @message_id=0,

              @severity=21,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 21', @operator_name=N'DBAs', @notification_method

= 1

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 22',

              @message_id=0,

              @severity=22,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 22', @operator_name=N'DBAs', @notification_method

= 1

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 23',

              @message_id=0,

              @severity=23,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 23', @operator_name=N'DBAs', @notification_method

= 1

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 24',

              @message_id=0,

              @severity=24,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 24', @operator_name=N'DBAs', @notification_method

= 1

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 25',

              @message_id=0,

              @severity=25,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

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


Rate

Share

Share

Rate