How to capture DeadLock for a database

This article briefly explains how to capture DEADLOCK error that occurs at one database. In many cases, we want to know where the deadlock occurs for a specific target database.

(1) Start SQL Profiler
(2) Connect to SQL server
(3) In [Trace Properties] dialog, select [TSQL-Locks] template in [Use this template].

(4) In [Trace Properties] dialog, click [Events Selection] tab. Uncheck all unwanted events. I tend to uncheck all events except [Deadlock graph] when profiling against production server to reduce noise.
(5) In [Events Selection] grid, select [Database Name] column header.
(6) In [Edit Filter] dialog, expand [Like] and type your target database name. Click OK.

(7) Click [Run] to start profiling.
(8) Once captured all deadlock information you need, click [Stop] toolbar button.
(9) To save trace, save .trc file using File->Save.

(10) Now to review deadlock graph, click [Deadlock graph] row in the grid and the graph will be shown at the bottom. By rightclicking in the graph pane, you can select [Zoom to fit] to adjust size. A circle with blue X is the deadlock victim. If you put mouse over the circle, you will see TSQL statement in the process was executing. Owner mode means the process (spid) owned the specific lock and Request mode means the process tried to get the lock but failed to get it.

(11) If deadlock graph is pretty complex, you might want to save the trace as XML file since it will have a lot more details. XML trace file contains all TSQL statements, call frames and resource lock relations. To save trace as XML, goto File->Save As -> Trace XML file. Reading XML trace file needs more detective work but sometimes deadlock graph UI does not provide clear view due to complex lock relationship and in that case reading XML trace will help a lot.