Configuring SQL Server Alerts

SQL Server Alerts

Click Here to see What is SQL Server Alerts?

To use alerts you must have Database Mail and a SQL Agent Operator configured. Most SQL instances I have come across already have Database Mail configured for job failure notifications.

Click here to see Enable and Configure Database Mail in SQL Server.

Now we will look for configuring the Operator. You can create the Operator using SSMS or T-SQL. Within SSMS expand SQL Server Agent, right click on Operator and chose New Operator. You will have a new dialog box open where you can give the operator a name and specify the email address to notify.

I prefer using T-SQL example code for creating the above Operator is as follows:

EXEC msdb.dbo.sp_add_operator @name = N'SQL_Alerts',
  @enabled = 1,
  @email_address = N'sql_alerts@mydomain.com';

Once you have Database Mail and the Operator configured you can create the alerts and assign them to the Operator.

19-25, 823-825 are critical errors and you will want to be notified when those errors arise.

If using SSMS, you can expand SQL Server Agent and then Alerts. By default, no alerts are created. If you right click and chose New Alert, you will get a screen similar to the figure below:

Configuring SQL Server Alerts
Severity 19 Error – Fatal error in resource

EXEC msdb.dbo.sp_add_alert @name = N'Severity 19 Error',
<pre>  @message_id = 0,   @severity = 19,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 19 Error',
  @operator_name = N'SQL_Alerts', @notification_method = 1;

Severity 20 Error – Fatal Error in Current Process

EXEC msdb.dbo.sp_add_alert @name = N'Severity 20 Error',
  @message_id = 0,   @severity = 20,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 20 Error',
  @operator_name = N'SQL_Alerts', @notification_method = 1;

Severity 21 Error – Fatal Error in Database Processes

EXEC msdb.dbo.sp_add_alert @name=N'Severity 21 Error',
  @message_id = 0,   @severity = 21,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 21 Error',
  @operator_name = N'SQL_Alerts', @notification_method = 1;

Severity 22 Error – Fatal Error: Table integrity Suspect

EXEC msdb.dbo.sp_add_alert @name = N'Severity 22 Error',
  @message_id = 0,   @severity = 22,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 22 Error',
  @operator_name = N'SQL_Alerts', @notification_method = 1;

Severity 23 Error – Fatal Error: Database Integrity Suspect

EXEC msdb.dbo.sp_add_alert @name = N'Severity 23 Error',
  @message_id = 0,   @severity = 23,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 23 Error',
  @operator_name = N'SQL_Alerts', @notification_method = 1;

Severity 24 Error – Fatal Error: Hardware Error

EXEC msdb.dbo.sp_add_alert @name = N'Severity 24 Error',
  @message_id = 0,   @severity = 24,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 24 Error',
  @operator_name = N'SQL_Alerts', @notification_method = 1;

Severity 25 Error – Fatal Error

EXEC msdb.dbo.sp_add_alert @name = N'Severity 25 Error',
  @message_id = 0,   @severity = 25,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 25 Error',
  @operator_name = N'SQL_Alerts', @notification_method = 1;

Error 823 – IO Error

EXEC msdb.dbo.sp_add_alert @name = N'Error 823',
  @message_id = 823,  @severity = 0,  @include_event_description_in = 0;

EXEC msdb.dbo.sp_add_notification @alert_name = N'Error 823',
  @operator_name = N'SQL_Alerts', @notification_method = 1;

Error 824 – Logical consistency error

EXEC msdb.dbo.sp_add_alert @name = N'Error 824',
  @message_id = 824,  @severity = 0,  @include_event_description_in = 0;

EXEC msdb.dbo.sp_add_notification @alert_name = N'Error 824',
  @operator_name = N'SQL_Alerts', @notification_method = 1;

Error 825 – Read retry required

EXEC msdb.dbo.sp_add_alert @name = N'Error 825',
  @message_id = 825,  @severity = 0,  @include_event_description_in = 0;

EXEC msdb.dbo.sp_add_notification @alert_name = N'Error 825',
  @operator_name = N'SQL_Alerts', @notification_method = 1;

 Once you execute above queries you will see the alerts are configured as below,

Configuring SQL Server Alerts


<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.