Eugene (10/22/2008)

---

GilaMonster :

You need set 1204 in combination with the 3605 flag:
The output of the flag will be sent to the SQL Server error log whenever SQL Server encounters a deadlock.

dbcc tracestatus(-1) -- returns nothing, so neither 3604 nor 3605 is enabled.
dbcc traceon (1204,-1)
CREATE TABLE Test1 (id int)
CREATE TABLE Test2 (id int)

BEGIN TRANSACTION
Insert into Test1 Values (1)

waitfor delay '00:00:20'

select * from Test2

BEGIN TRANSACTION
Insert into Test2 Values (1)

waitfor delay '00:00:20'

select * from Test1

DBCC TRACEON 1204, server process ID (SPID) 53. This is an informational message only; no user action is required.
Deadlock encountered .... Printing deadlock information
Wait-for graph

Node:1

RID: 12:1:3783:0 CleanCnt:2 Mode:X Flags: 0x3
Grant List 2:
Owner:0x00000000845CA180 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:51
SPID: 51 ECID: 0 Statement Type: SELECT Line #: 6
Input Buf: Language Event: BEGIN TRANSACTION

Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000083844E90 Mode: S SPID:52

Node:2

RID: 12:1:3503:0 CleanCnt:2 Mode:X Flags: 0x3
Grant List 3:
Owner:0x0000000080177DC0 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:52
SPID: 52 ECID: 0 Statement Type: SELECT Line #: 6
Input Buf: Language Event: BEGIN TRANSACTION

Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000088B48E90 Mode: S SPID:51

Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x0000000083844E90 Mode: S SPID:52