How To Enable Deadlock Trace in Sql Server

How to Enable Deadlock Trace

To enable deadlock trace we should do in 2 steps

a.      Enable from query analyzer

b.      Update startup parameters

1.      we should use below command

dbcc traceon (1222 ,-1)

dbcc traceon (3604 ,-1) 
dbcc traceon (3605 ,-1)

Trace Flag	Description
3604	Sends trace output to the client. Used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.
3605	Sends trace output to the error log.  (if SQL Server is started from CMD output also appears on the screen
1222	Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema. Scope: global only
1204	Returns the resources and types of locks participating in a deadlock and also the current command affected Scope is  global only

Enable deadlock trace

Here “dbcc  tracestatus” will give you list of traces enable rt now.

But this is not enough Bacause  once sql server is restarted this traces will not remain in picture any more. So if you want to enable these traces permanently and unaffected with sql services recyling, We have to add these in startup parametures

2.      Open sql server configuration manger  --> SQl services  --> properties --> Advance --> Properties -->

Startup parameters will be:

-dD:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eD:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lD:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-T1222; -T3604;  -T3605

To understand working of deadlock , please refer : Sql server deadlock Mechanism