How to Find and Alert on Deadlock Conditions

As with anything in SQL Server, there’s always multiple ways to approach any problem you come across. When looking to identify dead lock conditions for instance, some of your options include;

• SQL Server Profiler Trace
• SQL Server Extended Events Trace
• SQL Server Trace Flags
• SQL Server Alerts

There’s heaps of details examples available online which step you through setting up a SQL Profiler trace, and even an Extended Events trace via the new 2012 user interface. For reference purposes you can check out the following;

http://blogs.msdn.com/b/john_daskalakis/archive/2013/12/02/how-to-use-extended-events-to-proactively-monitor-your-sql-server-for-deadlock-issues.aspx

http://blogs.msdn.com/b/sqlserverfaq/archive/2013/04/27/an-in-depth-look-at-sql-server-memory-part-2.aspx

One thing however that many might not be aware is that Extended Events have actually been available in SQL Server since their 2008 release, however via a scripted interface only (which for most makes this much less desirable to use).. BUT, there is an add-on available which does bridge this gap for the command line impaired;

http://www.sqlskills.com/free-tools/sql-server-2012-extended-events-add-in/

Admittedly the interface isn’t quite as sexy as the 2012 EE interface, but definitely helps to sooth the burn for those point-and-click DBAs out there ?

SQL Server Trace Flags

SQL Server Trace Flags allow you to monitor for specific server conditions and log these events into the SQL server error log. For a full list of these flags (and noting that different flags may be SQL version specific), refer to:
http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx

Now to setup a SQL Trace on Deadlock conditions, the Trace Flags you’ll want to enable are 1204 and 1222. The following command enables both these flags globally on your SQL Server;

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

If you want to ensure these flags are enabled at time of starting SQL Server, you’ll need to add -T1204 and -T1222 to your instance’s start up parameters (done via SQL Server Configuration Manager). For more information on these flags, refer to http://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

SQL Server Alerts

If you are setting up SQL Server Alerts, there are a few which you’ll want to include. Make sure that you have your Database mail enabled and configured along with your SQL operations so that you get the notifications for these when they occur. The following script creates the alert conditions under SQL Agent. Once implemented you’ll need to open these and define the notification response (so that it emails the SQL operations) and alert messages you’d like to send.

The following Alert condition alerts on deadlock events;

USE [msdb]
GO
/****** Object:  Alert [General: Deadlock Event]    Script Date: 20/04/2014 09:07:12 ******/
EXEC msdb.dbo.sp_add_alert @name=N'General: Deadlock Event', 
@message_id=1205, 
@severity=0, 
@enabled=1, 
@delay_between_responses=60, 
@include_event_description_in=5, 
@category_name=N'[Uncategorized]', 
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

The following Alert condition alerts on lock allocation events;

USE [msdb]
GO
/****** Object:  Alert [General: Lock Allocation]    Script Date: 20/04/2014 09:08:25 ******/
EXEC msdb.dbo.sp_add_alert @name=N'General: Lock Allocation', 
@message_id=17125, 
@severity=0, 
@enabled=1, 
@delay_between_responses=60, 
@include_event_description_in=5, 
@category_name=N'[Uncategorized]', 
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

And finally, the following Alert condition alerts on lock request time-out events;

USE [msdb]
GO
/****** Object:  Alert [General: Lock request timeout period exceeded]    Script Date: 20/04/2014 09:08:45 ******/
EXEC msdb.dbo.sp_add_alert @name=N'General: Lock request timeout period exceeded', 
@message_id=1222, 
@severity=0, 
@enabled=1, 
@delay_between_responses=60, 
@include_event_description_in=5, 
@category_name=N'[Uncategorized]', 
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

Remember though, the above will only create the alerts under the SQL Agent – you’ll need to open each one to specify the alert response and notification message!

Happy Hunting!