How to trace Deadlock in SQL Server ?

Leave a comment (0) Go to comments

What is a DEAD LOCK in SQL Server?

When two or more transactions conflict in such a way that each is waiting for the other before they proceed. For instance, Transaction A might have a lock on Record 1 while trying to write to Record 2, while Transaction B has a lock on Record 2 while trying to write to Record 1. The two transactions will wait for each other forever unless the deadlock is somehow resolved.

This can be understood by following example.

How to trace Deadlock in SQL Server ? What is a DEAD LOCK in SQL Server trace sql server deadlock TRACE DEADLOCK TRACE all DEAD LOCK information sql server trace flag sql server startup tarce flag sql server deadlock trace flags sql server dead lock Keep track of DEADLOCK in SQL Server how to enable deadlock tracking sql server dbcc traceon 3605 DBCC TRACEON 1222 DBCC TRACEON 1204 DBCC TRACEON  1 dbcc traceon

How to Keep track of DEADLOCK in SQL Server ?

How to TRACE DEADLOCK in SQL Server ?

By Default SQL Server doesn’t keep track of DEAD LOCKs, we as a DBA need to request SQL Server to keep track of DEAD LOCKs in SQL Server Error Log.

While placing a request to SQL Server, we need to specify

  • What to Capture?
  • Where to capture ?
  • Define Scope, from where you want to capture ?

In this case, we wanted to capture DEADLOCK Information to SQL Server Log.


To TRACE all DEAD LOCK information to SQL Server Error Log with detailed information, we need to run the following statement. This will work till your SQL Server is running, once your services gets restarted, you need to do this again.

 

DBCC TRACEON (3605,1204,1222,-1)

/* 
-- where 
-- What to Capture?
--------------------
  -- (1204) = Capture Deadlock Events. 
  -- (1222) = Capture Deadlock Events with detailed Information (works only with SQL 2005 and higher version) 
-- Where to capture ?
-------------------
   -- (3605) = Capture the selected Events in the SQL Server error log. 
-- What is the Scope of Capture 
--------------------------------
    -- (-1) = Switches on the specified trace flags globally, that means do it globally, for all users/sessions
*/

This deadlock tracking will work till your SQL Server is running, once your services gets restarted, you need to do this again. If you wanted to enable this for permanently, then

  • Create a SQL Agent job and put this code into and schedule that job to when the Agent starts  or
  • You need to enable this trace flags at SQL Server startup parameter, by specifying  -T1222; -T3605;in the startup parameter.

If you liked this post then,

Subscribe to this Blog via Email:

Click here to Subscribe to FREE email updates from “DBATAG “, so that you do not miss out anything on SQL Server !!!

Tags : What is a DEAD LOCK in SQL Server,Keep track of DEADLOCK in SQL Server, trace sql server deadlock, sql server dead lock, sql server deadlock trace flags, how to enable deadlock tracking sql server,dbcc traceon,dbcc traceon 3605,DBCC TRACEON 3605,DBCC TRACEON 1204,DBCC TRACEON 1222,DBCC TRACEON -1,TRACE all DEAD LOCK information , sql server trace flag, sql server startup trace flag

EOF - How to trace Deadlock in SQL Server ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.