SQL Server deadlock notifications

The existence of a deadlock may be indicated by application slowness, error messages or cases when a SELECT statement takes a long time to finish or terminates prematurely with the message:

“Transaction (Process ID xx) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction”

All this indicates that SQL Server encountered a deadlock. A deadlock is a common issue with SQL Server. It is a circular blocking chain when two or more processes holding locks on data where each process wants to lock the data already locked by another process, and each process is waiting on the other process to complete before moving forward

Processes themselves aren’t able to resolve the conflict, and SQL Server will not allow this confrontation to continue indefinitely. It will choose one of the processes as the deadlock victim to kill and rollback that process, usually whichever will be the least expensive to be rolled back, so the other processes can move forward

The following types of resources can cause blocking that could result in a deadlock:

• Locks
• Worker threads
• Memory
• Parallel query execution-related resources
• Multiple Active Result Sets (MARS) resources

All of the resources listed in the section above participate in the Database Engine deadlock detection scheme. Deadlock detection is performed by a lock monitor thread that periodically initiates a search through all of the tasks in an instance of the database engine. The following points describe the search process:

• The default interval is 5 seconds
• When a deadlock is detected, the detection interval will drop from 5 seconds to as low as 100 milliseconds
• When the lock monitor thread cannot find new deadlock, the interval will go back to 5 seconds
• At the moment when a deadlock has just been detected, it’s anticipated that threads that must wait for a lock will be deadlocked as well. On deadlock detection, the first few locks waits will trigger a deadlock search immediately, without waiting for the next deadlock detection interval. For example, when the interval is 5 seconds, on a deadlock detection, the next lock wait will immediately start the deadlock detector. If this lock wait is part of a deadlock, it will be detected immediately without having to wait for a next deadlock search

In order to troubleshoot deadlocks, they must be identified and the most important thing to achieve this is raising an alert every time a deadlock occurs and notifying the database administrator. There are some native SQL Server options that could help identify that a deadlock has occurred and to raise an alert

Get notified when SQL Server deadlocks occur

Setting an alert seems quite simple via SQL Server Agent, but it is not due to the fact that errors related to deadlock are not logged by default. The SQL Server error 1205 is the most common error number associated with deadlocks, but the error 3928 also relates to SQL Server deadlocks. As a consequence of such SQL Server behavior, when the deadlock occurs, a predefined alert will not be triggered

So, 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 just to toggle the 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

Another way for forcing SQL Server to log the error messages related to deadlock is using the server-side trace, meaning that we have to turn on Trace Flags 1204 and 1222. This will ensure that above mentioned errors are logged, but in this way a quite verbose logging information will be stored into the logs. This can be done using DBCC statement:

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

Numerous trace flags are able to control what will be logged under different circumstances. Considering the fact that SQL Server can be natively used with plenty of different languages and that there can be a ton of different messages and/or errors that can occur in SQL Server, this is not quite a recommendable solution. As an example, in the image below is shown how menu different 1205 and 3928 messages exists in SQL Server, which is one of the reasons why the altering of the master.sys.messages table is a better solution

Setup deadlock alerts using the query script

Once that’s done, we can start to work on setting up alerts for deadlocks. There are two ways to set up SQL Server Agent alerts – using the SQL Server Management Studio’s GUI or using the stored procedure sp_add_alert. In case that alert must be sent each time a deadlock take place to a database administrator, then an operator and email address have to be set as well. The easiest way to do that is using the sp_add_operator stored procedure

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

To use the query to create the alert for errors 1205 and 3828, and send an email whenever a deadlock is encountered, the below code could be used:

USE msdb
GO

EXEC msdb.dbo.sp_add_operator
@name = N'ApexSQL Database Administrators',
	@enabled = 1,
	@email_address = N'DBA@ApexSQL.com',
	@pager_address = N'6035158954@pager.ApexSQL.com',
    @weekday_pager_start_time = 080000,
    @weekday_pager_end_time = 170000,
    @pager_days = 62 ;
	
EXEC msdb.dbo.sp_add_alert
	@name = N'Error #1205: Deadlock detected',
		@message_id = 1205,
		@severity = 0,
		@enabled = 1,
@include_event_description_in=3
		@delay_between_responses = 0,
		@include_event_description_in = 1;
GO

EXEC msdb.dbo.sp_add_notification
        @alert_name = N'Error #1205: Deadlock detected',
        @operator_name = N'Database Administrator',
        @notification_method = 3;
GO

EXEC msdb.dbo.sp_add_alert
	@name = N'Error #3928: Deadlock detected',
		@message_id = 3928,
		@severity = 0,
		@enabled = 1,
@include_event_description_in=3
		@delay_between_responses = 0,
		@include_event_description_in = 1;
GO

EXEC msdb.dbo.sp_add_notification
        @alert_name = N'Error #3928: Deadlock detected',
        @operator_name = N'Database Administrator',
        @notification_method = 3;
GO

Once the script is run you should see the Operator and Alerts listed

Setup deadlock alerts using the SQL Server Management Studio GUI

To set up deadlock alerts via SQL Server Management Studio:

1. Start SQL Server Management Studio

2. Expand the SQL Server Agent three view in the Object Explorer pane, right click on Operators and select New Operator from the context menu

   

3. In the New Operator dialog that appears, set the operator name and notification options. In the General tab, there are three Notification options that can be set – E-mail name, Net send address, and Pager e-mail name

   

Note: Net send will not work unless the computer has a working network connection, even in case when the net send source and destination are the same computer. For Net send to work, the Messenger service on SQL Server must be started on both machines – that sending and receiving the Net send message. The Messenger service is discontinued by Microsoft in Windows Vista/Server 2008 and newer and considering all this various issues with Net send, the much better option is use email and/or pager alerts

Now, we can create deadlock alerts:

1. Right click on Alerts in the Object Explorer pane and select the New Alert from the context menu

   

2. In the New Alert dialog, set the required parameters for the General section like in the images below for the error 1205. Create the adequate alert in the same way for error 3928 as well

   

3. In the Response section select the Notify operators. Select the operator and check the E-mail and Pager check boxes

   

4. In the Include alert error text in section of the Options dialog, check only the E-mail and Pager checkboxes. This will ensure that an appropriate error message text is included within the email or pager message body

   

Check your newly created operator and alerts settings

Now, check the properties of the created operator to ensure the email address and pager are set

Also, verify in the Notifications dialog that all the alert notifications are configured

By checking the properties on a particular alert, you should see that the error number matches the alert name

To check the number of raised deadlock alerts and the date and time when the last alert was triggered, just open the alert History tab

After all, this has been set, an alert will be triggered on any deadlock that occurs and an appropriate email and pager message will be sent to a database administrator. Being aware that a deadlock has occurred, the database administrator will be still required to dig through the SQL Server or to set up some additional ways for catching the information about the particular deadlock and to identify the deadlock victims

Used resources:

https://technet.microsoft.com/en-us/library/ms178104%28v=sql.105%29.aspx
https://msdn.microsoft.com/en-us/library/ms187382.aspx
https://msdn.microsoft.com/en-us/library/ms175094.aspx
https://msdn.microsoft.com/en-us/library/ms189531.aspx
https://msdn.microsoft.com/en-us/library/ms186747.aspx