Forum Topic

Thanks for your participation and contribution.

Back to SQL Server

Deadlock Information from System Health Session

Post a reply
Deadlock Extended Events Session

--Paths for the Extended Event Sessions 

SELECT, ISNULL(cfn.CurrentFileName, 'Not Running') AS CurrentFileName, REPLACE(CAST(esf.value AS VARCHAR(256)), '.xel', '*.xel') AS FilePath
FROM sys.server_event_sessions es
INNER JOIN sys.server_event_session_fields esf ON es.event_session_id = esf.event_session_id
LEFT JOIN ( SELECT, CAST( t.target_data AS XML ).value('(EventFileTarget/File/@name)[1]', 'VARCHAR(MAX)') AS CurrentFileName
FROM sys.dm_xe_sessions s
LEFT JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
WHERE t.target_name = 'event_file') cfn
ON =
WHERE = 'filename' ;

XEvent.value('(@timestamp)[1]', 'datetime') as UTC_event_time,
XEvent.query('(data/value/deadlock)') AS deadlock_graph
SELECT CAST(event_data AS XML) as [target_data]
FROM sys.fn_xe_file_target_read_file('system_health_*.xel',NULL,NULL,NULL)
WHERE object_name like 'xml_deadlock_report'
) AS [x]
CROSS APPLY target_data.nodes('/event') AS XEventData(XEvent)

Last updated

1-1 of 1

Reply to this discussion

You cannot edit posts or make replies: You should be logged in before you can post.