Forum Topic

Thanks for your participation and contribution.


Back to SQL Server

Deadlock Information from System Health Session

Post a reply
1953 views
i90runner1
i90runner1
Deadlock Extended Events Session
 

--Paths for the Extended Event Sessions 

SELECT es.name, 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 s.name, 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 es.name = cfn.name
WHERE esf.name = 'filename' ;

SELECT
XEvent.value('(@timestamp)[1]', 'datetime') as UTC_event_time,
XEvent.query('(data/value/deadlock)') AS deadlock_graph
FROM
(
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.