How to Find Deadlock Information from SQL Server

2014/02/012 min read
bookmark this
Responsive image

Table of Contents

  1. Find deadlock information from sql server
  2. Fix deadlock
  3. Reference

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

SQL

More Blogs Like This