How to Find Deadlock Information from SQL Server

Table of Contents

• Introduction
• Find Deadlock Information from SQL Server
• Fix Deadlock
• Reference
• Conclusion

Introduction

There are many ways to find SQL Server deadlocks or resolve deadlock issues. In this blog, I will show how to use SQL Server Profiler to detect deadlocks and one example of how to fix them.

Find Deadlock Information from SQL Server

• Open SQL Server Profiler

• Create a new Trace file

• At the General tab, check "Save to file" and save the file to a shared folder. This is because after stopping the trace, you can analyze the trace log information.

• At the Events Selection tab, expand Locks and check "Deadlock graph". Trace Properties will show an "Events Extraction Settings" tab; then click "Save Deadlock XML events separately" and save the file to a shared folder.

• Click "Run"

• If you know where the application causes the deadlock, then go to the application and reproduce the error.

• Stop the trace.

• Go to the trace file you saved to the shared folder and search for "dead". You should find the deadlock XML.

Fix Deadlock

My issue was fixed by running the following SQL command:

EXEC sp_updatestats

Reference

• http://blogs.msdn.com/b/saponsqlserver/archive/2015/01/18/deadlock-analysis-examples.aspx

• http://blogs.msdn.com/b/saponsqlserver/archive/2013/01/31/deadlock-analysis-basic.aspx

• http://blog.sqlauthority.com/2007/05/16/sql-server-fix-error-1205-transaction-process-id-was-deadlocked-on-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-rerun-the-transaction/

• http://sqlmag.com/database-performance-tuning/gathering-deadlock-information-deadlock-graph

Conclusion

SQL Server Profiler is a useful tool for detecting deadlocks. By saving trace logs and deadlock XML events, you can analyze the root cause and apply fixes such as updating statistics with sp_updatestats.