How to capture deadlocks using system_health Extended Events

An application using SQL Server as the database backend was experiencing some application rollbacks. I decided to investigate the SQL Server to identify any errors which could be correlated to the application timeouts experienced by the users. 

I started reviewing the errors in the Extended Events system health logs, which are normally running by default on a SQL Server. They have a ton of useful information . I noticed a steady stream of deadlocks . This is the code used to create a permanent table to store the deadlock details , for review by the application team. 

Extracting details from Extended Events can be challenging but there there is an  Extended Events function -   sys.fn_xe_file_target_read_file , which  will parse the xel file.  The xel file is  a reference to the file name extension of the event file target. 

In the code below - I'm creating a permanent table - to store the data for future use - but I also need to dynamically grab the location the xel file to parse the data. I grab that data by using the sp_readerrorlog procedure. Derive the file path , place in a variable and then use the @path variable to construct the full file path of the event data file (xel).

CREATE TABLE DeadlockLog
(dealock_info XML,
logDate DATETIME)

CREATE TABLE #deadlocklog (
            LogDate DATETIME 
            , ProcessInfo VARCHAR(100)
            , [Text] VARCHAR(MAX)
            );
DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX);
INSERT INTO #deadlocklog EXEC sp_readerrorlog;
SELECT @tag = text
FROM #deadlocklog 
WHERE [Text] LIKE 'Logging%MSSQL\Log%';
DROP TABLE #deadlocklog;
SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29);
INSERT INTO DeadlockLog
SELECT 
  CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockDetails,
  CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime') 
  AS Execution_dateTime
FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME like 'xml_deadlock_report';

Read more on Deadlocks 

Number of deadlocks since last SQL Server restart

SQL Server deadlocks analysis