Deadlock Detection and Reporting part-I

November 21, 2014

This will be a 2 part series about deadlock. The first part will be about how you detect deadlock in SQL Server and collect that information. The second part will be about reporting from the collected information.

A deadlock is a transient event until one of the processes becomes a victim. Unless you persist all the details about the deadlock and get notified it is not easy to solve a problem. This process will help you detecting deadlock as it happens, get details about the deadlock and also find the frequency. Eventually, you will be able to identify if there is a trend/pattern of deadlock in your environment.

You can use a built-in agent alert leveraging WMI query.

Create a built-in alert.

USE [MSDB];
GO
EXEC msdb.dbo.sp_add_alert @name=N'WMI -  Query DEADLOCK_GRAPH', 
		@message_id=0, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		--Default Instance
		@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
		--Named Instance
		--@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\NamedInstanceName',
		@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
@job_name=N'Process Once - Capture Deadlock Graph'
GO;
GO

Create a table in your DBA database.

USE [DBA]
GO
CREATE TABLE [dbo].[DeadlockEvents]
    (
      [Id] [int] IDENTITY(1, 1)
                 NOT NULL ,
      [AlertTime] [datetime2](7) NOT NULL ,
      [DeadlockGraph] xml NULL
    )
ON  [PRIMARY]
GO
ALTER TABLE [dbo].[DeadlockEvents] ADD  CONSTRAINT [DF_AlertTime]  DEFAULT (SYSDATETIME()) FOR [AlertTime]
GO

In the response tab of your alert, you call a SQL agent job. That job will execute the following code.

1st Step:

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Process Once - Capture Deadlock Graph', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/*  Step [Insert Deadlock Information to DeadlockEvents Table]  ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert Deadlock Information to DeadlockEvents Table', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'INSERT INTO DeadlockEvents(DeadlockGraph)
VALUES (N''$(ESCAPE_SQUOTE(WMI(TextData)))'')', 
		@database_name=N'msdb', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

I will have the 2nd step for this job and more on deadlock reporting in the 2nd part of the blog.

3 replies on “Deadlock Detection and Reporting part-I”

I have setup your code but keep getting the same error. I am using SQL server 2016. Unable to start execution of step 1 (reason: Variable WMI(TextData) not found). The step failed.

Right-click on SQL Agent–>Properties–>AlertSystem. At the bottom under ‘Token replacement’, check the box for ‘Replace tokens for all job responses to alerts’

Leave a Reply to David Waller Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.