Setting up Email Alerts for SQL Server Deadlocks

There are two main types of Alerts that can be configured within SQL Server. Event Alerts which can be used to track actions within SQL such as critical failures or failed login attempts. Performance Condition Alerts which are more robust and can be used to alert on specific performance related issues.
This post mainly focused on triggering an email alerts when a deadlock occurs on SQL Server instance.

Step #1: Configuring 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 Configuring Database Mail in SQL Server.

Step #2: Enable flag to log deadlock events

The first thing that has to be done is instructing SQL Server to log these errors. One and probably the best way is to alter the master.sys.messages table via the column is_event_logged, which controls whether or not an error is logged in the SQL Server log. To ensure the deadlock related errors are logged, all we have to do is to set is_event_logged flag to true for all 1205 and 3928 messages. This can be done using the sp_altermessage stored procedure:

EXEC master.sys.sp_altermessage 1205, 'WITH_LOG', TRUE;
GO
EXEC master.sys.sp_altermessage 3928, 'WITH_LOG', TRUE;
GO


Step #3 Configure alerts and notifications

Error 1205 is the most common error number associated with a deadlock. Errors 1211 and 3928 are also associated with deadlocks within SQL Server.
Here, we are configuring alerts for 1205 and 3928 error and set up a notification for an alert using a script below.

--Creating alert for error number 1205
EXEC msdb.dbo.sp_add_alert
@name = N'Error #1205: Deadlock detected',
@message_id = 1205,
@severity = 0,
@enabled = 1,
@delay_between_responses = 0,
@include_event_description_in = 1;
GO

-- Setting up notification for error number 1205
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error #1205: Deadlock detected',
@operator_name = N'DeadlockAlerts',
@notification_method = 3;
GO

--Creating alert for error number 3928
EXEC msdb.dbo.sp_add_alert
@name = N'Error #3928: Deadlock detected',
@message_id = 3928,
@severity = 0,
@enabled = 1,
@delay_between_responses = 0,
@include_event_description_in = 1;
GO

-- Setting up notification for error number 3928
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error #3928: Deadlock detected',
@operator_name = N'Database Administrator',
@notification_method = 3;
GO

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


Setting up alert for error number 1205





Setting up alert for error number 3928






Step #4: Create SQL Server Operator

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

USE msdb
GO
EXEC msdb.dbo.sp_add_operator
@name = N'JobAlerts',
@enabled = 1,
@email_address = N'ksamir26@gmail.com'
GO

This can also be done using SSMS as shown below




Step #5: Enable trace flags

In this step we enabled trace flag 1222 and trace flag 1204 on an instance of SQL Server to capture deadlock information. 
Trace Flag 1204: Focused on the nodes involved in the deadlock. Each node has a dedicated section, and the final section describes the deadlock victim.
Trace Flag 1222: Returns information in an XML-like format that does not conform to an XML Schema Definition (XSD) schema. The format has three major sections. The first section declares the deadlock victim. The second section describes each process involved in the deadlock. The third section describes the resources that are synonymous with nodes in trace flag 1204.

DBCC TRACEON (1204, -1)
DBCC TRACEON (1222,-1)


Once these alerts are set up, we will verify this alerts by creating a simple deadlock scenario on the server as shown below.Lets, create two user tables with name Table1 and Table2. Open two different query windows Session 1 and Session 2.

--Create two tables and insert a record
CREATE TABLE TABLE1
(
       ID int,
       FIRSTNAME varchar(20)
);
GO
CREATE TABLE TABLE2
(
       ID int,
       LASTNAME varchar(20)
);
GO
INSERT INTO TABLE1 VALUES (1,'SAMIR')
INSERT INTO TABLE2 VALUES (1,'KAZI')
GO

In Session 1, run the following query
--Run the below query in session 1
BEGIN TRAN;
UPDATE dbo.Table1
SET FIRSTNAME = 'MIKE'
WHERE ID = 1;

And in Session 2 run the below command
--Run the below query in session 2
BEGIN TRAN;
UPDATE dbo.Table2
SET LASTNAME = 'SMITH'
WHERE ID = 1;

At this point both table 1 and 2 are blocked by two different sessions. To create a deadlock, go back to Session 1 and execute the below command

In Session 1, run the following query
--Run the below query in session 1
UPDATE dbo.Table2
SET LASTNAME = 'SMITH'
WHERE ID = 1;

Above query will not complete and it will stay as executing. Run the following query into Session 2

In Session 2 run the below command
--Run the below query in session 2
UPDATE dbo.Table1
SET FIRSTNAME = 'MIKE'
WHERE ID = 1;

One of the session will be picked as the deadlock victim, and the other will complete without any hint of a problem as shown below


Email will be triggered as deadlock occurred. We have trace enabled in step 4 to capture the details related to deadlock.


Have a look on the output of trace flag 1222 below.


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