Identify Deadlocks Using Graphical Deadlock Chain Event in SQL Server Profiler

This article explains how to identify deadlocks in SQL Server using graphical deadlock chain event in SQL Server Profiler.

We would highly recommend you to read “Identify Deadlocks in SQL Server Using Trace Flag 1222 and 1204” to learn more about how to write to SQL Server Error Logs in the event of a Deadlock in SQL Server.

How to Capture Deadlock Events in SQL Server Using SQL Profiler

Step 1: Once you have opened SQL Server Profiler. Click File -> New Trace -> Connect to SQL Server Instance to open up Trace Properties window as shown in the snippet below.

Step 2: In the Trace Properties window specify Trace Name and choose Standard Template and then click on Events Selection Tab.

Step 3: In the Events Selection Tab choose the respective Events as shown in the snippet below. Most importantly Deadlock Graph, Lock: Deadlock, Lock: Deadlock Chain and the “Run” button to start the SQL Server Profiler Trace.

Step 4: If you wish to save Deadlock XML separately then go to Events Extraction Settings tab and select the checkbox “Save Deadlock XML events separately” and specify the file path.

Trending SQL Server Disaster Recovery Articles and Tips

• How to Repair Suspect Database in SQL Server
• SQL Server: How to Start SQL Server with Minimal Configuration
• How to Start SQL Server in Single User Mode?
• How to Identify the Location of Resource Database in SQL Server
• What are Virtual Log Files in SQL Server Transaction Log File?
• How to Backup and Restore Resource Database in SQL Server

Step 5: In the below snippet you could see that SQL Server Profiler has captured a deadlock event and the information is represented in a Graphical Format for easier understanding. The deadlock graph shows that Server Process ID 51 was the victim when there was a deadlock between Server Process ID 65. Move the cursor over the Server Process ID’s to view the TSQL script run be each of the SPID’s.

Step 6: SQL Server Profiler Deadlock Graph Event is shown in the below snippet.

Learn How to Enable and Disable Trace Flags 1204 and 1222 to Capture deadlock events in SQL Server Error Logs.

Conclusion

In this article you have seen how to identify deadlocks using graphical deadlock chain event in SQL Server Profiler to get a detailed overview of the events which lead to the deadlock.