Configure Critical Email Alerts in SQL Server

SQL Server has alerts that are more important based on the severity of the error. When an error is raised by the SQL Server Database Engine, the severity of the error indicates the type of problem encountered by SQL Server.
Error raised from severity level 17 to 24 raised are typically important and refers to overall SQL Server health.The following table lists and describes the severity levels of the errors raised from 17 to 24 by the SQL Server Database Engine.

17
This severity indicates that an operation making SQL Server out of resources or exceeding defined limit. That may be disk space or lock limit.
18
This error represents nonfatal internal software error
19
This error represents some non-configurable internal limit has been exceeded and the current batch process is terminated. To be very frank, I have not seen this severity practically in my life.
20
This severity indicates current statement has encountered a problem and because of this severity level client connection with SQL Server will be disconnected.
21
This severity indicates that you have encountered a problem that affects all processes in the current database.
22
This error indicates problem with database table or index. It may be corrupt or damaged.
23
This error indicates problem with database integrity which may be fixed by DBCC command.
24
This error indicates problem with the hardware of SQL Server. Need to check disk drive and related hardware extensively.
823
The 823 error message usually indicates that there is a problem with underlying storage system or the hardware or a driver that is in the path of the I/O request.
824
Indicates logical Input/Output (I/O) error. Logical consistency error' clearly indicates damage due to corruption in the database where corruption is due to an I/O subsystem component that is faulty.
825
Referred to as the read-retry warning. This error lets you know that a retry of the operation was needed and how many times SQL Server had to retry the attempt before it was successful.


Demo on Configuring Critical SQL Server Alerts

This demo covers all alerts for severity 17 through 24 as well as 823 through 825 which are lesser known I/O error alerts

Step #1: Configure Database Mail

The first thing that has to be done is to setup database mail with a profile. The profile can be named anything but in these instructions, the profile name Performance Alerts is referenced. For procedures in setting up database mail, see this post Setting up Database Mail for SQL Server

Step #2: Create SQL Server Operator

Create an operator (notification recipient) for use with alerts.

USE msdb;
GO
--Create sql server operator
EXEC msdb.dbo.sp_add_operator
      @name = 'CriticalAlerts',
      @enabled = 1,
      @email_address = 'ksamir26@gmail.com';
GO

Same step can also be done using SQL Server Management Studio as shown below



Perform either step#3 or Step#4 to setup alerts and notifications.

Step #3 Configure alerts and notifications using script

Here, we are setting up alerts for severity level raised from 17 to 24 and error number 823,824,825 by the SQL Server Database Engine.

USE [msdb]
GO
--Severity Level 17: Insufficient Resources
EXEC msdb.dbo.sp_add_alert
       @name='Severity Level 17',
       @enabled=1,
       @message_id=0,
       @severity=17,
       @delay_between_responses=60,
       @include_event_description_in=1
GO
--Add notification to an alert
EXEC msdb.dbo.sp_add_notification
       @alert_name='Severity Level 17',
       @operator_name='CriticalAlerts',
       @notification_method = 1
GO
--Severity Level 18: Nonfatal Internal Error Detected
EXEC msdb.dbo.sp_add_alert
       @name='Severity Level 18',
       @enabled=1,
       @message_id=0,
       @severity=18,
       @delay_between_responses=60,
       @include_event_description_in=1
GO
--Add notification to an alert
EXEC msdb.dbo.sp_add_notification
       @alert_name='Severity Level 18',
       @operator_name='CriticalAlerts',
       @notification_method = 1
GO

--Severity Level 19: SQL Server Error in Resource
EXEC msdb.dbo.sp_add_alert
       @name='Severity Level 19',
       @enabled=1,
       @message_id=0,
       @severity=19,
       @delay_between_responses=60,
       @include_event_description_in=1
GO
--Add notification to an alert
EXEC msdb.dbo.sp_add_notification
       @alert_name='Severity Level 19',
       @operator_name='CriticalAlerts',
       @notification_method = 1
GO

--Severity Level 20: SQL Server Fatal Error in Current Process
EXEC msdb.dbo.sp_add_alert
       @name='Severity Level 20',
       @enabled=1,
       @message_id=0,
       @severity=20,
       @delay_between_responses=60,
       @include_event_description_in=1
GO
--Add notification to an alert
EXEC msdb.dbo.sp_add_notification @alert_name='Severity Level 20',
       @operator_name='CriticalAlerts',
       @notification_method = 1
GO

--Severity Level 21: SQL Server Fatal Error in Database (dbid) Processes
EXEC msdb.dbo.sp_add_alert
       @name='Severity Level 21',
       @enabled=1,
       @message_id=0,
       @severity=21,
       @delay_between_responses=60,
       @include_event_description_in=1
GO
--Add notification to an alert
EXEC msdb.dbo.sp_add_notification
       @alert_name='Severity Level 21',
       @operator_name='CriticalAlerts',
       @notification_method = 1
GO

--Severity Level 22: SQL Server Fatal Error Table Integrity Suspect
EXEC msdb.dbo.sp_add_alert
       @name='Severity Level 22',
       @enabled=1,
       @message_id=0,
       @severity=22,
       @delay_between_responses=60,
       @include_event_description_in=1
GO
--Add notification to an alert
EXEC msdb.dbo.sp_add_notification
       @alert_name='Severity Level 22',
       @operator_name='CriticalAlerts',
       @notification_method = 1
GO

--Severity Level 23: SQL Server Fatal Error: Database Integrity Suspect
EXEC msdb.dbo.sp_add_alert
       @name='Severity Level 23',
       @enabled=1,
       @message_id=0,
       @severity=23,
       @delay_between_responses=60,
       @include_event_description_in=1
GO
--Add notification to an alert
EXEC msdb.dbo.sp_add_notification
       @alert_name='Severity Level 23',
       @operator_name='CriticalAlerts',
       @notification_method = 1
GO

--Severity Level 24: Hardware Error
EXEC msdb.dbo.sp_add_alert
       @name='Severity Level 24',
       @enabled=1,
       @message_id=0,
       @severity=24,
       @delay_between_responses=60,
       @include_event_description_in=1
GO
--Add notification to an alert
EXEC msdb.dbo.sp_add_notification
       @alert_name='Severity Level 24',
       @operator_name='CriticalAlerts',
       @notification_method = 1
GO

--Severity Level 823: I/O Error
EXEC msdb.dbo.sp_add_alert @name='Error Number 823',
@enabled=1,
@message_id=823,
@severity=0,
@delay_between_responses=60,
@include_event_description_in=1
GO
EXEC msdb.dbo.sp_add_notification @alert_name='Error Number 823',
       @operator_name='JobAlerts',
       @notification_method = 1
GO

--Severity Level 824: I/O Error
EXEC msdb.dbo.sp_add_alert @name='Error Number 824',
@enabled=1,
@message_id=824,
@severity=0,
@delay_between_responses=60,
@include_event_description_in=1
GO
EXEC msdb.dbo.sp_add_notification @alert_name='Error Number 824',
       @operator_name='JobAlerts',
       @notification_method = 1
GO

--Severity Level 825: I/O Error with Read-retry.
EXEC msdb.dbo.sp_add_alert @name='Error Number 825',
@enabled=1,
@message_id=825,
@severity=0,
@delay_between_responses=60,
@include_event_description_in=1
GO
EXEC msdb.dbo.sp_add_notification @alert_name='Error Number 825',
       @operator_name='JobAlerts',
       @notification_method = 1
GO

Step #4 Configuring alerts and notifications using GUI

Above step can be also performed using SQL Server Management Studio as shown below. Here, we are configuring alert for severity level 17





Repeat step #4 to setup alerts for other severity levels using SSMS

Verify that all the alert notifications are configured.




Verify SQL Server Agent Configuration
Note: Check the SQL Server Agent configuration. Make sure SQL Server Agent is stared and configured to start automatically. SQL Server Agent drives alerting and if it’s not running, alerts will not be raised




No comments:

Post a Comment