Configuring deadlock and Storing Deadlock events in a table

SQL Server 2012 and above.

Verification –

Ensure Extended Event “system_health” is running.

SSMS, checking Management -> Extended Events -> Sessions. These should be green arrow which indicate its running.

 

The system_health session writes to two targets, both an event file and the ring buffer:

 

Right click on package0.event_file and click on View Target Data…

Configuring Deadlock Event Id and logging.

Step 1 –

Enable deadlock logging

–TO CHECK IF LOGGING IS ENABLED (is_event_logged)
SELECT * FROM sys.messages WHERE language_id = 1033 AND message_id = 1205

–TO ALTER THE LOGGING PARAMETER
EXEC sp_altermessage @message_id = 1205, @parameter = ‘WITH_LOG’ ,@parameter_value = ‘true’
GO

Step 2 –

Create SQL Server Job called “Deadlock Capture Job” and add step with script called “Deadlock_Capture_Script”

SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

DECLARE @LastDeadLock DATETIME2, @XE_EVENT_FILE_TARGET XML, @XE_FILE_PATH NVARCHAR(260)

SELECT @XE_EVENT_FILE_TARGET = CONVERT(XML, xst.target_data)
FROM sys.dm_xe_sessions xs
JOIN sys.dm_xe_session_targets xst
ON xs.address = xst.event_session_address
WHERE xs.name = ‘system_health’
AND xst.target_name = ‘event_file’

SELECT @XE_FILE_PATH = t.c.value(‘File[1]/@name’, ‘varchar(260)’) FROM @XE_EVENT_FILE_TARGET.nodes(‘/EventFileTarget’) AS t(c)

SELECT CAST(event_data AS XML) AS event_data
INTO #DEADLOCKS_DATA
FROM sys.fn_xe_file_target_read_file(@XE_FILE_PATH, null, null, null)
WHERE object_name = ‘xml_deadlock_report’

IF (SELECT OBJECT_ID(‘DEADLOCKS_Details’)) IS NULL BEGIN
CREATE TABLE DEADLOCKS_Details
(
DeadlockTimestamp DATETIME2 NOT NULL,
DeadlockEvent NVARCHAR(250) NOT NULL,
VictimProcessId NVARCHAR(250) NOT NULL,
Processid_1 NVARCHAR(250) NOT NULL,
WaitResource_1 NVARCHAR(250) NULL,
WaitTime_1 INT NULL,
OwnerId_1 BIGINT NULL,
CurrentDb_1 INT NULL,
LastTranStarted_1 DATETIME2 NULL,
LastBatchStarted_1 DATETIME2 NOT NULL,
Statement_1 NVARCHAR(MAX) NULL,
Client_1 NVARCHAR(250) NULL,
Hostname_1 NVARCHAR(250) NULL,
Loginname_1 NVARCHAR(250) NULL,
Processid_2 NVARCHAR(250) NOT NULL,
WaitResource_2 NVARCHAR(250) NULL,
WaitTime_2 INT NULL,
OwnerId_2 BIGINT NULL,
CurrentDb_2 INT NULL,
TranStarted_2 DATETIME2 NULL,
LastBatchStarted_2 DATETIME2 NOT NULL,
Statement_2 NVARCHAR(MAX) NULL,
Client_2 NVARCHAR(250) NULL,
Hostname_2 NVARCHAR(250) NULL,
Loginname_2 NVARCHAR(250) NULL
)
END

SELECT @LastDeadLock = ISNULL(MAX(DeadlockTimestamp), ‘1900-01-01’) FROM DEADLOCKS_Details

;WITH CTE_EVENTS AS
(
SELECT
c.value(‘@timestamp’, ‘DATETIME2’) AS DeadlockTimestamp,
c.value(‘(@name)[1]’, ‘nvarchar(250)’) AS DeadlockEvent,
c.value(‘(data/value/deadlock/victim-list/victimProcess)[1]/@id’, ‘nvarchar(250)’) AS VictimProcessId,
c.value(‘(data/value/deadlock/process-list/process)[1]/@id’, ‘nvarchar(250)’) AS Processid_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@waitresource’, ‘nvarchar(250)’) AS WaitResource_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@waittime’, ‘int’) AS WaitTime_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@ownerId’, ‘bigint’) AS OwnerId_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@currentdb’, ‘int’) AS CurrentDb_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@lasttranstarted’, ‘datetime2’) AS LastTranStarted_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@lastbatchstarted’, ‘datetime2’) AS LastBatchStarted_1,
c.value(‘(data/value/deadlock/process-list/process/inputbuf)[1]’, ‘nvarchar(max)’) AS Statement_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@clientapp’, ‘nvarchar(250)’) AS Client_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@hostname’, ‘nvarchar(250)’) AS Hostname_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@loginname’, ‘nvarchar(250)’) AS Loginname_1,
c.value(‘(data/value/deadlock/process-list/process)[2]/@id’, ‘nvarchar(250)’) AS Processid_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@waitresource’, ‘nvarchar(250)’) AS WaitResource_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@waittime’, ‘int’) AS WaitTime_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@ownerId’, ‘bigint’) AS OwnerId_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@currentdb’, ‘int’) AS CurrentDb_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@lasttranstarted’, ‘datetime2’) AS LastTranStarted_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@lastbatchstarted’, ‘datetime2’) AS LastBatchStarted_2,
c.value(‘(data/value/deadlock/process-list/process/inputbuf)[2]’, ‘nvarchar(max)’) AS Statement_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@clientapp’, ‘nvarchar(250)’) AS Client_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@hostname’, ‘nvarchar(250)’) AS Hostname_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@loginname’, ‘nvarchar(250)’) AS Loginname_2
FROM #DEADLOCKS_DATA
CROSS APPLY event_data.nodes(‘//event’) AS t (c)
)
INSERT DEADLOCKS_Details
SELECT *
FROM CTE_EVENTS
WHERE DeadlockTimestamp > @LastDeadLock

DROP TABLE #DEADLOCKS_DATA

Select the database from Dropdown

Step 3 –

Create deadlock Alerts in SQL Server Agent-