How to use trace flag 1204

-- =====================================================
--How to use trace flag 1204
--
--Jiangjian 2005.08 (please keep this information for reference)
-- =====================================================

-- =====================================================
/*--description

Trace token 1204 is used to return the type of lock that participates in the deadlock and the currently affected command. Deadlock information is automatically sent to the error log.
Turn on trace tag using DBCC traceon, the 3rd parameter is specified as-1, which means not only for the current connection, but for all connection that include future builds
Turn off trace flags using DBCC Traceoff

The following is a demo of using trace tag 1204 in Query Analyzer, where deadlock information is recorded in the SQL Server log and can be viewed in the following ways:
Enterprise Manager--expand instance--manage--SQL Server Log
Because some of the log information is truncated when viewed in Enterprise Manager, it is recommended that you view the ERRORLOG file directly in the \mssql\log directory using a text viewing tool such as Notepad

Detailed instructions for deadlock information refer to the go--url--in the online Help (Books Online-menu) Enter the following address:
MK: @MSITStore: c:\program%20files\microsoft%20sql%20server\80\tools\books\trblsql.chm::/tr_servdatabse_5xrn.htm
--*/
-- =====================================================

--Test environment
Use tempdb
Go

CREATE TABLE ta (id int)
INSERT ta SELECT 1

CREATE TABLE TB (id int)
INSERT TB SELECT 1
Go

--Open the deadlock record
DBCC Traceon (1204,3605,-1)
Go

--Create a deadlock (make a new connection, copy the code and execute)
SET lock_timeout-1
SET deadlock_priority Low
SET TRANSACTION Isolation Level
Repeatable READ
BEGIN TRAN
SELECT * from Ta WHERE id=1
WAITFOR DELAY ' 00:05:00 '
UPDATE TB SET id=2 WHERE id=1
COMMIT TRAN
Go

--Close the deadlock record
DBCC Traceoff (1204,3605)
Go

--Clear Test
DROP TABLE TA,TB
Go

--1204 generated logging information
2005-08-25 08:16:21.85 Spid4 node:1
2005-08-25 08:16:21.85 spid4 rid:2:1:28:0 cleancnt:2 mode:u
2005-08-25 08:16:21.85 spid4 Grant List 0::
2005-08-25 08:16:21.85 spid4 owner:0x1998aec0 mode:s flg:0x0 ref:1 life:02000000 spid:52 ECID:0
2005-08-25 08:16:21.85 spid4 spid:52 ecid:0 Statement Line #: 1
2005-08-25 08:16:21.85 spid4 Input buf:language event:set lock_timeout-1
SET TRANSACTION Isolation Level
Repeatable READ
BEGIN TRAN
SELECT * from TB WHERE id=1
WAITFOR DELAY ' 00:01:00 '
UPDATE ta SET id=2 WHERE id=1
COMMIT TRAN

2005-08-25 08:16:21.85 Spid4 Requested by:
2005-08-25 08:16:21.85 spid4 restype:lockowner stype: ' OR ' mode:x spid:51 Ec: (0x19b5b558) value:0x1997b2c0 cost: (1/0)

2005-08-25 08:16:21.85 Spid4 Node:2
2005-08-25 08:16:21.85 spid4 rid:2:1:15:0 cleancnt:2 mode:u
2005-08-25 08:16:21.85 spid4 Grant List 0::
2005-08-25 08:16:21.85 spid4 owner:0x1997b3e0 mode:s flg:0x0 ref:1 life:02000000 spid:51 ECID:0
2005-08-25 08:16:21.85 spid4 spid:51 ecid:0 Statement Line #: 1
2005-08-25 08:16:21.85 spid4 Input buf:language Event:
SET lock_timeout-1
SET deadlock_priority Low
SET TRANSACTION Isolation Level
Repeatable READ
BEGIN TRAN
SELECT * from Ta WHERE id=1
WAITFOR DELAY ' 00:01:00 '
UPDATE TB SET id=2 WHERE id=1
COMMIT TRAN

2005-08-25 08:16:21.85 Spid4 Requested by:
2005-08-25 08:16:21.85 spid4 restype:lockowner stype: ' OR ' mode:x spid:52 Ec: (0x1a24d558) value:0x1998cfa0 cost: (0/0)
2005-08-25 08:16:21.85 spid4 victim Resource Owner:
2005-08-25 08:16:21.85 spid4 restype:lockowner stype: ' OR ' mode:x spid:51 Ec: (0x19b5b558) value:0x1997b2c0 cost: (1/0)

--Analysis of logging information, to analyze node:1 as an example,--** annotated is the description
--** NODE:X represents the project number (x) in a chain of deadlocks.
2005-08-25 08:16:21.85 Spid4 node:1
2005-08-25 08:16:21.85 spid4 rid:2:1:28:0 cleancnt:2 mode:u
--** Lists, can be Authorization (grant), conversion (convert), and waiting (wait), and grant List enumerates the currently authorized owners.
2005-08-25 08:16:21.85 spid4 Grant List 0::
2005-08-25 08:16:21.85 spid4 owner:0x1998aec0 mode:s flg:0x0 ref:1 life:02000000 spid:52 ECID:0
--** identifies the system process ID thread in the context of a parallel process. The entry spid x ECID 0 represents the main thread, while the SPID x ECID > 0 represents the child thread of the same SPID.
--** Statement Type: statement types
--** Line #: The row number of the statement being executed when a deadlock occurs
2005-08-25 08:16:21.85 spid4 spid:52 ecid:0 Statement Line #: 1
--** input BUF Lists all statements in the current batch.
2005-08-25 08:16:21.85 spid4 Input buf:language Event:
SET lock_timeout-1
SET TRANSACTION Isolation Level
Repeatable READ
BEGIN TRAN
SELECT * from TB WHERE id=1
WAITFOR DELAY ' 00:01:00 '
UPDATE ta SET id=2 WHERE id=1
COMMIT TRAN

2005-08-25 08:16:21.85 Spid4 Requested by:
--** Mode Specifies the type of lock for a specific resource that the thread requests, authorizes, or waits for. The pattern can be is (intent sharing), S (shared), U (update), IX (Intent Exclusive), SIX (exclusive with intent), and X (exclusive)
2005-08-25 08:16:21.85 spid4 restype:lockowner stype: ' OR ' mode:x spid:51 Ec: (0x19b5b558) value:0x1997b2c0 cost: (1/0)