DEAD LOCK

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 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.
  • SQL Server Configuration Manager >>> SQL Server Services>>> right-click SQL Server ()>>> Properties >>> Advanced TAB >>> Startup Parameters box >>> type the parameters separated by semicolons (;).
  • In SQL Server Denali (2011), this is very easy, check here to understand how to change startup parameters in SQL Server Denali (2011)