Deadlock Troubleshooting Trace 1222

OPEN 1222 TRACE

DBCC TRACEON– Enable trace flags.
DBCC TRACEOFF- Disable trace flags.
DBCC TRACESTATUS- Display the status of trace flags.

–> Turn flag on for only the session level

DBCC TRACEON (1222)

DBCC TRACEON (1205,1222)
GO

–> Turn flag on globally at server for all the sessions

DBCC TRACEON (1222, -1 )
GO

– >Turn flag off

DBCC TRACEOFF (1222);

DBCC TRACEOFF (1222, -1 );

–> Show flag status

DBCC TRACESTATUS (1205,1222)

DBCC TRACESTATUS (-1)

Get the sql server error log when deadlock happen

Approach one

2. Approach 2

Get the deadlock information

deadlock-list
deadlock victim=process1f121ab0
process-list
   process id=process1f121ab0 taskpriority=0 logused=0 waitresource=KEY: 105:72057594038910976 (0c006459e83f) waittime=4344 ownerId=341136033 transactionname=INSERT EXEC lasttranstarted=2010-11-07T15:17:10.493 XDES=0x191d6cc0 lockMode=S schedulerid=1 kpid=6012 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2010-11-07T15:17:10.493 lastbatchcompleted=2010-11-07T15:17:10.480 clientapp=Microsoft SQL Server Management Studio - Query hostname=DUR-DESKTOP1 hostpid=3304 loginname=sa isolationlevel=read committed (2) xactid=341136033 currentdb=105 lockTimeout=4294967295 clientoption1=673187936 clientoption2=390200
    executionStack
     frame procname=deadlocktest.dbo.p1 line=4 stmtstart=90 sqlhandle=0x0300690020c96c7e14d1fb00279e00000100000000000000
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
     frame procname=adhoc line=4 stmtstart=78 stmtend=152 sqlhandle=0x020000000ff6b2216ef7807b9ee77cf22e74f4e2cf9e35b5
INSERT INTO #t1 EXEC p1 4
    inputbuf
       WHILE (1=1) BEGIN
         INSERT INTO #t1 EXEC p1 4
         TRUNCATE TABLE #t1
       END
   process id=process1014c1c0 taskpriority=0 logused=312 waitresource=KEY: 105:72057594038910976 (0b00274d9328) waittime=4344 ownerId=341136042 transactionname=UPDATE lasttranstarted=2010-11-07T15:17:10.510 XDES=0x191d6280 lockMode=X schedulerid=1 kpid=3640 status=suspended spid=58 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2010-11-07T15:17:09.587 lastbatchcompleted=2010-11-07T15:16:56.283 clientapp=Microsoft SQL Server Management Studio - Query hostname=DUR-DESKTOP1 hostpid=3304 loginname=sa isolationlevel=read committed (2) xactid=341136042 currentdb=105 lockTimeout=4294967295 clientoption1=673187936 clientoption2=390200
    executionStack
     frame procname=deadlocktest.dbo.p2 line=6 stmtstart=188 sqlhandle=0x0300690059ed607f19d1fb00279e00000100000000000000
UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1
     frame procname=adhoc line=7 stmtstart=154 stmtend=178 sqlhandle=0x0200000093bb11328a642e3b8a34dde3fe79d2d055d62825
EXEC p2 4
    inputbuf
USE deadlocktest
       SET NOCOUNT ON
       WHILE (1=1)
         EXEC p2 4
resource-list
   keylock hobtid=72057594038910976 dbid=105 objectname=deadlocktest.dbo.t1 indexname=idx1 id=lock2409ca00 mode=X associatedObjectId=72057594038910976
    owner-list
     owner id=process1014c1c0 mode=X
    waiter-list
     waiter id=process1f121ab0 mode=S requestType=wait
   keylock hobtid=72057594038910976 dbid=105 objectname=deadlocktest.dbo.t1 indexname=idx1 id=lock2409ddc0 mode=S associatedObjectId=72057594038910976
    owner-list
     owner id=process1f121ab0 mode=S
    waiter-list
     waiter id=process1014c1c0 mode=X requestType=wait