This configuration was setup on Windows 2012 and SQL SERVER 2012, it was also tested on SQL SERVER 2008.
Create a table named DeadlockEvents, this will be used to store the date and time stamp of the deadlock event as well as the XML generated from the deadlock event.
STEP 1.
Create a table to hold the SQL SERVER deadlocks. One field will contain the time at which the deadlock occurred and the other the XML from the deadlock.
/**
Create Table to hold deadlocks
**/
USE DBA ;
GO
IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL
BEGIN
DROP TABLE DeadlockEvents ;
END ;
GO
CREATE TABLE DeadlockEvents
(AlertTime DATETIME, DeadlockGraph XML) ;
GO
STEP 2.
Add a SQL Agent job to trigger an insert of a deadlock record when a deadlock event occurs.
The following fields will need to be populated with your environment specific information.
@owner_login_name=N’DOMAIN\sa-job-owner’
/** Add JOB**/
USE [msdb]
GO
/****** Object: Job [DBM2_Capture_Deadlock_Graph] Script Date: 11/13/2014 13:54:07 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 11/13/2014 13:54:07 ******/
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'DBM2_Capture_Deadlock_Graph',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job for responding to DEADLOCK_GRAPH events',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'DOMAIN\sa-job-owner', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Insert graph into LogEvents] Script Date: 11/13/2014 13:54:08 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert graph into LogEvents',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@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
(AlertTime, DeadlockGraph)
VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')',
@database_name=N'DBA',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [send email] Script Date: 11/13/2014 13:54:08 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'send email',
@step_id=2,
@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'exec sp_deadlock_alert',
@database_name=N'DBA',
@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
STEP 3.
This stored procedure is run when the SQL Agent job is triggered. I sends a copy of the deadlock XML to the email address specified as the recipient.
/** Add stored proc **/
use DBA;
GO
create procedure sp_deadlock_alert
AS
DECLARE @profile varchar(30)
set @profile = (select top 1 name from msdb.dbo.sysmail_profile)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = '[email protected]',
@body = 'DeadLock Graph',
@subject = 'A deadlock has occurred, please review the provided attachment...',
@body_format = 'TEXT',
@query_attachment_filename = 'Deadlockgraph.xml',
@attach_query_result_as_file = 1,
@query_no_truncate = 1,
--@query_result_width = 512,
--@query_result_no_padding = 1,
@query = 'SELECT TOP 1 [DeadlockGraph]
FROM [DBA].[dbo].[DeadlockEvents]
order by alerttime desc
for xml PATH(''ROW''), root(''ROOT''), TYPE';
STEP 4.
Add alert to fire SQL Agent job when a deadlock event occurs.
The job named DBM2_Capture_Deadlock_Graph will file whenever a deadlock event occurs.
For this to work you token replacement enabled on the SQL AGENT.
Right-click SQL Server Agent in Object Explorer, select Properties, and on the Alert System page, select Replace tokens for all job responses to alerts to enable these tokens.
/** Add Alert **/
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
@job_name='DBM2_Capture_Deadlock_Graph' ;
GO
STEP 5.
Testing the deadlock alerting
Open a SSMS query window and execute the following SQL.
use dba;
BEGIN TRAN
update table_a set ID=ID where ID = 100;
update table_b set ID=ID where ID =100;
Open a second SSMS query window and execute the following SQL.
This should result in a deadlock which inserts a record into the DeadlockEvents table and sends an email alerting you of the deadlock.
use dba;
BEGIN TRAN
update table_b set ID=ID where ID =100;
update table_a set ID=ID where ID = 100;
Tater's Tech Blog 