check deadlocking info in SQL server
To get deadlocking info in log , one of my practice is:
I enabled the trace flag
DBCC TRACEON(1204, -1)
DBCC TRACEON(1222, -1)
----select deadlocks
SELECT
xed.value('@timestamp', 'datetime2(3)') as CreationDate,
cast(xed.query('.').value('(/event/data/value/text())[1]', 'nvarchar(MAX)') AS xml) AS XEvent
FROM
(
SELECT CAST([target_data] AS XML) AS TargetData
FROM sys.dm_xe_session_targets AS st
INNER JOIN sys.dm_xe_sessions AS s
ON s.address = st.event_session_address
WHERE s.name = N'system_health'
AND st.target_name = N'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)
ORDER BY CreationDate DESC
also uses Amit's method https://troubleshootingsql.com/2012/09/06/system-health-session-and-deadlocks/
Comments
Post a Comment