All 7 Deadlocks questions
More in Deadlocks
What is a deadlock in SQL Server, and why does it happen? How does SQL Server decide which transaction becomes the deadlock victim? What error does an application receive when its transaction is chosen as a deadlock victim, and how should the application respond? What is the difference between a deadlock and normal blocking, and how do you tell them apart when troubleshooting a slow application? How would you read a deadlock graph to identify the exact queries and resources that caused a specific deadlock? What design changes can you make to application code and database access patterns to reduce the frequency of deadlocks?
Advertisement
Back to results for “Error Handling”
SQL Server › Deadlocks

How do you capture detailed information about deadlocks that are happening on a server so you can investigate their root cause?

Intermediate

Answer

You set up an Extended Events session capturing the xml_deadlock_report event, which automatically records full details about every deadlock that occurs, including the exact queries, resources, and sessions involved, giving you everything needed to diagnose the problem after it happens rather than needing to catch it live.
CREATE EVENT SESSION DeadlockCapture ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file (SET filename = 'Deadlocks.xel')
WITH (STARTUP_STATE = ON);
Real-world example A database administrator sets up a permanent deadlock capturing session, finally getting the full details needed to fix a deadlock that had been happening randomly for weeks without anyone catching it in the act.

Common follow-ups: What information is included in a deadlock graph?;Can SQL Server Profiler also capture this same deadlock information?

SQL Server Profiler & Extended Events;Error Handling with TRY CATCH
All 7 Deadlocks questions