How To Capture Deadlock Using Profiler In SQL Server : Demo

Capturing deadlock is pretty easy with coming versions of SQL Server. You can use below methods for same.

1. Deadlock Trace flag 
2. Extended Events
3. Using SQL server Profiler
4. Using code at application level.

Here I am going to explain how you can capture deadlock using Profiler.

To capture deadlock in profiler we have to run sql server profiler till deadlock occurred in sql server and if there was no deadlock occur than you have to keep running profiler.

When i say profiler , you can choose any of server side trace or profiler documented in link.

Profiler works without the trace flags being turned on and there are three events that can be captured for deadlocks.  Each of these events is in the Locks event class.

§Deadlock graph - Occurs simultaneously with the Lock. The Deadlock Graph event class provides an XML description of the deadlock.

§Lock: Deadlock - Indicates that two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources that the other transaction owns.

§Lock: Deadlock Chain - Is produced for each of the events leading up to the deadlock.

Steps:

1. Start --> All Programs --> Microsoft Sql Server --> Performance Tools --> SQL Server Profiler
2. Or you can refer link "here"
3. Open new trace, select events  Lock (Deadlock graph , Deadlock , Deadlock Chain)(also shown in below screen shot)

4. Start trace and monitor trace. Once deadlock occur (you can also create deadlock) you will capture some thing like below 

5. Here you can see we will get SPID , database id , page id , locks deadlock victim and all details at run time for detailed analysis instead reading lot of lines captured by trace flags.


Recommended Reads:
How Sql server Deadlock Mechanism works
Enable Deadlock Trace
Reproduce Deadlock in SQL Server