They Both Needed Each Other’s Lock
Deadlocks are not random. They are deterministic — the same access patterns will produce them every time.
The problem is that SQL Server resolves them silently. One session gets chosen as the victim, receives error 1205, and the application retries (or crashes). Nobody logged it. Nobody investigated.
Until now.
What This Query Does
SQL Server’s system_health Extended Events session has been running in the background since SQL Server 2008.
By default it captures deadlock graphs automatically — no configuration needed.
This query reads those graphs from the ring buffer and extracts:
| Column | What It Tells You |
|---|---|
Deadlock Time | When it happened |
Victim Process | The session SQL Server killed to resolve it |
Spid 1 / 2 | The two sessions involved |
Client App 1 | Which application sent the query |
Host 1 | Which server it came from |
Proc 1 | Which stored procedure was executing |
Deadlock Graph | The full XML — paste into SSMS for a visual diagram |
The full Deadlock Graph XML column can be saved and opened as .xdl in SSMS to see the graphical deadlock diagram with arrows showing lock ownership and requests.
Note: The ring buffer holds only the most recent events. For long-term deadlock history, configure an XE session to write to a file target.
Deadlock History Script
Loading…
Gareth Winterman