DBA Scripts: Get Deadlock Summary

The application logged Msg 1205 and you need to know which 2 statements collided, on what, and which one lost. SQL Server already recorded the deadlock graph in the built-in system_health Extended Events session, and this script reads it back.

Keep the Process ID: it is how you find the victim in the graph. If the script returns no rows for a deadlock you know happened, go to If It Returns No Rows.

Why Deadlock Summary Matters

Deadlocks are rarely a one-off; the same pair of statements deadlocking against each other usually means a consistent access pattern (different order of operations across two code paths) that will keep happening until someone changes the code or the indexing:

• Every deadlock victim’s transaction is rolled back entirely, and unless the caller retries, that work is lost
• A deadlock graph shows exactly which two statements were fighting over which resources, the starting point for an actual fix
• Deadlocks that go uninvestigated tend to recur, since nothing about the underlying access pattern changed
• The system_health session captures every deadlock graph by default, so nobody has to set up a trace before the deadlock happens

When to Run This Script

• An application error mentioning “deadlock victim” or Msg 1205
• Routine SQL Server health checks, to catch deadlocks nobody reported
• After a code or indexing change intended to reduce lock contention, to confirm it worked
• Reviewing a server you’ve just inherited, to see its recent deadlock history

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-DeadlockSummary
Category    : performance-troubleshooting
Purpose     : Show recent deadlock events from the system_health XEvent ring buffer.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-deadlock-summary/)
Requires    : VIEW SERVER STATE
Notes       : Ring buffer holds recent events only (typically last 30–60 min). For full
              history, query the system_health .xel files directly in SSMS.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

WITH ring_buffer AS (
    SELECT
        CAST(target_data AS XML) AS ring_xml
    FROM sys.dm_xe_session_targets AS t
    INNER JOIN sys.dm_xe_sessions AS s ON t.event_session_address = s.address
    WHERE s.name = 'system_health'
      AND t.target_name = 'ring_buffer'
),
deadlock_nodes AS (
    SELECT
        e.x.value('@timestamp', 'datetime2') AS event_timestamp,
        e.x.query('data[@name="xml_report"]/value') AS deadlock_graph
    FROM ring_buffer
    CROSS APPLY ring_xml.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS e(x)
)
SELECT TOP 50
    event_timestamp,
    deadlock_graph
FROM deadlock_nodes
ORDER BY event_timestamp DESC;

It reads the system_health session’s ring_buffer target through sys.dm_xe_session_targets and returns up to 50 xml_deadlock_report events, newest first: the timestamp and the full deadlock graph. It does not read the event files on disk.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check for recent deadlocks:
.\run.ps1 Get-DeadlockSummary

# To run against a remote sql server:
.\run.ps1 Get-DeadlockSummary -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/performance/blocking-locking/Get-DeadlockSummary.sql
• powershell/wrappers/performance/blocking-locking/Get-DeadlockSummary.ps1

Example Output

3 deadlocks on the lab instance, 12 seconds apart, then the script, with the date dropped and each graph cut after its victim-list:

The top row is the Msg 1205 above: its victim, process220c9532868, carries spid="75" in the graph’s process-list. Both sessions ran the same table, dbo.Accounts, in opposite order, and the resource-list shows each one holding an X key lock the other wanted.

These rows took 9 to 11 minutes to appear. Until then the script returned nothing for them, and 4 earlier lab deadlocks never appeared at all, so read 0 rows before you trust an empty result.

Understanding the Results

If It Returns No Rows

The same session also writes to system_health*.xel files in the instance’s Log folder, and every lab deadlock was in them within a minute. Read them with sys.fn_xe_file_target_read_file; it returns the same 2 columns as the script:

SET QUOTED_IDENTIFIER ON;

SELECT TOP 50
    CAST(event_data AS XML).value('(event/@timestamp)[1]', 'datetime2') AS event_timestamp,
    CAST(event_data AS XML).query('event/data[@name="xml_report"]/value/deadlock') AS deadlock_graph
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report'
ORDER BY event_timestamp DESC;

It reads every file, so it is slower: between 3 and 101 seconds on this lab instance against 10 files and 397 MB, under 1 second of it CPU, where the script took under 1 second to 87. MS Docs gives Standard and Enterprise 10 files of up to 100 MB, and that held 26 days of events here. A busy or noisy instance holds fewer.

Best Practices

• Treat a recurring deadlock on the same two statements as a design problem (access order, indexing, transaction scope), not something to just keep retrying past.
• Access tables in a consistent order across all code paths where possible; MS Docs lists it first under minimize deadlocks.
• Keep transactions short and avoid user interaction or slow calls inside an open transaction, since longer transactions hold locks longer and raise deadlock odds.
• Confirm the application actually retries on error 1205. A deadlock without a retry is a failed operation, not just a delayed one.

Related Scripts

You may also find these scripts useful:

• Blocking and Locking (hub)
• Blocking Chains
• Blocking Chains with Plan / Contention Analysis / Lock Escalation Stats
• Blocking Sessions / Blocking Summary
• Open Transactions
• Reading and Fixing a SQL Server Deadlock, the Msg 1205 walkthrough once you have the graph
• Collect Blocking and Deadlock Events, an Agent job that stores deadlock graphs in a table on a schedule
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Summary

Deadlocks are one of the few SQL Server errors that come with a built-in explanation, if you know where to look. The system_health session captures every deadlock graph; this script reads the recent ones from memory, and the event files hold the history once memory has moved on.

Run this script whenever an application reports a deadlock error, and periodically as part of routine health checks to catch the ones nobody reported.