How to Find Deadlock Information from SQL Server

There're many way to find sql server deadlock or resolve the issue. At this blog, I will show how to use SQL Server Profiler to show deadlock or one example how to fix it.

Find deadlock information from sql server

• Open SQL Server Profiler
• Create new Trace file
• At General Tab check Save to file, and save file to shared folder. This is because after stop trace, you can analytic the trace log information.
• At Events Selection tab, expend Locks and check "Deadlock graph". Trace Properties will show "Events Extraction Settings" tab, then click "Save Deadlock XML events separately" and save file to shared folder.
• Click "run" 
• If you know where the application cause the dead lock then go to the application and re cause the error.
• Stop the trace.
• Go to the trace file you saved to shared folder, and search "dead", you should find the deadlock xml. 

Fix deadlock

My issue was fixed by run 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