Trace flags

To enable these trace flags, run the following commands: (you must be a member of the SQL Server system administrators' role to run the DBCC command)

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

The 1204 flag returns the types of locks participating in the deadlock encountered.

The 1205 flag returns more detailed information regarding the statements being run at the time of the deadlock.

The 3605 flag sends the trace information to the error log, which the SQLDiag tool will later output to a text file.

The "-1" parameter to the command tells SQL Server to apply the trace flag to all connections to the server.

----------

Disables the specified trace flags.

DBCC TRACEOFF

---------

Displays the status of trace flags.

DBCC TRACESTATUS

*******************************************

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

DBCC TRACESTATUS

DBCC TRaceOff(1204,1205,3605,-1)------Sql Server 2000

-------------

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

DBCC TRaceOff(1204,1222,3605,-1)------Sql Server 2005

If you’re running SQL 2005, you should be using 1222 instead of 1204 unless you have deep-seated masochistic tendencies. Alternatives to 1222:

If you are using SQL 2000 or SQL 7.0, you’ll have no choice but to fall back on the older -T1204.
There’s a “Deadlock graph” Profiler trace event that provides the same info as -T1222. Feel free to use this instead of -T1222 if you’re on SQL 2005.

But don’t waste your time with the “Lock:Deadlock” and “Lock:Deadlock Chain” trace events that are in SQL 2000, as they provide an unacceptably incomplete picture of the deadlock.