How To Get SQL Server deadlocks using system_health extended event

 How To see parameter value on xml_deadlock_report?

This article will help us to retrieve the SQL Server deadlock information using the default system-health extended event session.You may consider it as a black box recorder to track the SQL instance activities.We do not need to enable trace flags 1204 and 1222 to capture the deadlocks using this extended event session. Simply run the below SQL query for see the each deadlock report in the XML file.


T-SQL Query:

DECLARE @xelfilepath NVARCHAR(260)

SELECT @xelfilepath = dosdlc.path

FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc;

SELECT @xelfilepath = @xelfilepath + N'system_health_*.xel'


 SELECT CONVERT(XML, event_data) AS EventData

        INTO #TempTable FROM sys.fn_xe_file_target_read_file(@xelfilepath, NULL, NULL, NULL)

         WHERE object_name = 'xml_deadlock_report'

SELECT EventData.value('(event/@timestamp)[1]', 'datetime2(7)') AS UtcTime,


      EventData.value('(event/@timestamp)[1]', 'VARCHAR(50)')), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS LocalTime,

            EventData.query('event/data/value/deadlock') AS XmlDeadlockReport

     FROM #TempTable

     ORDER BY UtcTime DESC;

After execute this query, you will get the following deadlock information. Now click on each deadlock report and view the XML information in details. You can also save the required XML deadlock report in XSD format.

I hope this will help you.


Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog -
My Books - Book 1 and Book 2 Powered by Blogger.