How to Use trace flag 1204

-- ===================================================== ====================
-- How to Use trace tag 1204
--
-- Producer build 2005.08 (reference please keep this information)
-- ===================================================== ====================

-- ===================================================== ====================
/* -- Description

The trace mark 1204 is used to return the type of the lock involved in the deadlock and the currently affected command. The deadlock information is automatically sent to the error log.
When the trace flag is enabled, dbcc traceon is used. The first parameter is-1, indicating that it is not only for the current connection, but for all connections including future connections.
Disable trace flag using DBCC TRACEOFF

The following is a demo of using trace mark 1204 in the query analyzer. The deadlock information is recorded in the SQL Server log. You can view it in the following method:
Enterprise Manager -- expand instance -- manage -- SQL Server Log
Some LOG information is truncated when viewed in the Enterprise Manager. Therefore, we recommend that you use a text viewing tool such as NotePad to directly view the ERRORLOG file under the \ MSSQL \ LOG directory.

For more information about deadlocks, see online help (books online -- GO--URL in the 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

-- Enable the deadlock record

Dbcc traceon (1204,3605,-1)
GO

-- Deadlock (query window)

SET LOCK_TIMEOUT-1
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

-- Deadlock (query window B)

SET LOCK_TIMEOUT-1
SET DEADLOCK_PRIORITY LOW
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ
BEGIN TRAN
SELECT * FROM tb WHERE id = 1
UPDATE ta SET id = 2 WHERE id = 1
COMMIT TRAN
GO

-- Disable the deadlock record

Dbcc traceoff (1204,3605)
GO

-- Clear test

Drop table ta, tb
GO

-- 1204 logs generated

08:16:21. 85 spid4 Node: 1
08:16:21. 85 spid4 RID: 2: 1: 28: 0 CleanCnt: 2 Mode: U Flags: 0x2
08:16:21. 85 spid4 Grant List 0 ::
08:16:21. 85 spid4 Owner: 0x1998aec0 Mode: S Flg: 0x0 Ref: 1 Life: 02000000 SPID: 52 ECID: 0
08:16:21. 85 spid4 SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 1
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

08:16:21. 85 spid4 Requested:
08:16:21. 85 spid4 ResType: LockOwner Stype: 'OR' Mode: x spid: 51 ECID: 0 Ec :( 0x19B5B558) Value: 0x1997b2c0 Cost :( 1/0)

08:16:21. 85 spid4 Node: 2
08:16:21. 85 spid4 RID: 2: 1: 15: 0 CleanCnt: 2 Mode: U Flags: 0x2
08:16:21. 85 spid4 Grant List 0 ::
08:16:21. 85 spid4 Owner: 0x1997b3e0 Mode: S Flg: 0x0 Ref: 1 Life: 02000000 SPID: 51 ECID: 0
08:16:21. 85 spid4 SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 1
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

08:16:21. 85 spid4 Requested:
08:16:21. 85 spid4 ResType: LockOwner Stype: 'OR' Mode: x spid: 52 ECID: 0 Ec :( 0x1A24D558) Value: 0x1998cfa0 Cost :( 0/0)
08:16:21. 85 spid4 Victim Resource Owner:
08:16:21. 85 spid4 ResType: LockOwner Stype: 'OR' Mode: x spid: 51 ECID: 0 Ec :( 0x19B5B558) Value: 0x1997b2c0 Cost :( 1/0)

-- Analyze the log record information and take Node: 1 as an example. -- ** indicates

-- ** Node: x indicates the project number (x) In the deadlock chain ).
08:16:21. 85 spid4 Node: 1
08:16:21. 85 spid4 RID: 2: 1: 28: 0 CleanCnt: 2 Mode: U Flags: 0x2
-- ** Lists, which can be Grant, Convert, and Wait. The Grant List Lists the authorized owner.
08:16:21. 85 spid4 Grant List 0 ::
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 case of parallel processes. Entry SPID x ECID 0 indicates the main thread, while SPID x ECID> 0 indicates the sub-thread with the same SPID.
-- ** Statement Type: Statement Type
-- ** Line #: the row number of the statement being executed when a deadlock occurs.
08:16:21. 85 spid4 SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 1
-- ** Input Buf: lists all statements in the current batch.
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

08:16:21. 85 spid4 Requested:
-- ** Mode specifies the lock type for a specific resource requested, authorized, or waiting by a thread. The modes can be IS (intention sharing), S (SHARE), U (update), IX (intention exclusive), SIX (exclusive share with intention), and X (exclusive)