Deadlock Troubleshooting-Trace 1222

Open1222 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 trace eoff (1222,-1 );

GO

-> Show flag status

Dbcc tracestatus (1205, 1222)

Dbcc tracestatus (-1)

 

Get the SQL server error log when deadlock happen

1. 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 = T15: 17: 10.493 XDES = 0x191d6cc0 lockMode = S schedulerid = 1 kpid = 6012 status = sushortded 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 = 0x0300690020c96c7e14d1fb00279e0000000000000000000
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @ p1 AND @ p1 + 1
Frame procname = adhoc line = 4 stmtstart = 78 stmtend = 152 sqlhandle = 0x0200020.ff6b2216ef7807b9ee77cf22e74f4e2cf9e35b5
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 = suincluded 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 = 0x0300690059ed607f19d1fb00279e0000000000000000000
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