dataiotech Labs
Scripts Library/sqlserver/#48
Script #48
sqlserver
Lockingread-onlyMin Version: 2012

Read Recent SQL Server Deadlocks from system_health

Pulls deadlock graphs from the system_health Extended Events session. Lets you analyze past deadlocks without enabling trace flags.

#deadlock#extended-events#system_health
sqlserver · sql
SELECT TOP 20
       x.event_data.value('(event/@timestamp)[1]', 'datetime2') AS event_time,
       x.event_data.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_graph
FROM (
  SELECT CAST(target_data AS XML) AS target_data
  FROM sys.dm_xe_session_targets st
  JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
  WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer'
) AS tgt
CROSS APPLY tgt.target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS q(n)
CROSS APPLY (SELECT CAST(q.n.query('.') AS XML)) AS x(event_data)
ORDER BY event_time DESC;
Operational Notes & Remediation Guidance

Deadlocks captured in the ring buffer can be parsed into XML deadlocks graphs for detailed locking analysis.

Production Safety & Execution Standards
  • This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
  • Prior to executing on production systems under high load, verify the target database version (Min. Version: 2012).
  • Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.

Engineering Triad: Related Runbooks & Incident Response

Which production incidents and emergency drills execute this script as a diagnosis step?

Runbook Library (63)
Related Alerts & Metrics
M29SQL Server Deadlock RateSQL Server