sql server deadlock trace flag 1204

Hi Friends,

This is my 15th blog on SQL Server Trace Flag 1204 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.

Deadlock is a very common problem for all the DBA. First phase of deadlock resolution is Deadlock detection. So for deadlock detection, Microsoft provides a trace flag 1204 that can write deadlock information to sql server error log. You can analyze that information later to prevent it in future.

Trace flag 1204 provides node base information about deadlock in another words you can say that all nodes which are involved in deadlock. Finally after all nodes information it also provides information about deadlock victim.

Now I’ll show you the output of trace flag 1204 by creating a deadlock. Below script will only creates database, tables and also insert data into them. It will also do error log recycle and enable the trace flag 1204.

use master
go
Create database Trace1204
go
use Trace1204
go
create table xttrace1
(
id int identity(1,1),
fname varchar(50),
lname varchar(50),
city varchar(100)
)
go
insert into xttrace1 values('prince','rastogi','moradabad')
go
insert into xttrace1 values('deep','pandey','haldwani')
go
insert into xttrace1 values('ashok','kumar','hydrabad')
go
insert into xttrace1 values('keshav','mahapatra','cuttack')
go
select * into xttrace2 from xttrace1
go
DBCC TRACEON(1204,-1)
go
exec sp_cycle_errorlog
go

Now open a new query window (first session) in management studio and run below query:

use Trace1204
go
begin tran

update xttrace1
set fname='ashwani'
where city='moradabad'

waitfor delay '00:00:30'

update xttrace2
set fname='raveesh'
where city='cuttack'

commit tran

Again open a new query window (second session) in management studio and run below query:

use Trace1204
go
begin tran

update xttrace2
set fname='raveesh'
where city='cuttack'

waitfor delay '00:00:30'

update xttrace1
set fname='ashwani'
where city='moradabad'

commit tran

In my case, first session completed successfully while second session selected as deadlock victim. Second session gave me below as output.

(1 row(s) affected)

Msg 1205, Level 13, State 45, Line 11
Transaction (Process ID 54) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.

Due to trace flag 1204, you can read the detailed information from sql server error log now.

Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
RID: 15:1:296:0                CleanCnt:2 Mode:X Flags: 0x3
Grant List 0:
Owner:0x04C80580 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:56 ECID:0 XactLockInfo: 0x0B1AD84C
SPID: 56 ECID: 0 Statement Type: UPDATE Line #: 9
Input Buf: Language Event: begin tran

update xttrace1
set fname='ashwani'
where city='moradabad'

waitfor delay '00:00:30'

update xttrace2
set fname='raveesh'
where city='cuttack'

commit tran
Requested by: 
ResType:LockOwner Stype:'OR'Xdes:0x0B1AD0F8 Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x1987245C) Value:0x13695f80 Cost:(0/268)
NULL
Node:2
RID: 15:1:300:3                CleanCnt:2 Mode:X Flags: 0x3
Grant List 0:
Owner:0x04C7FD40 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x0B1AD11C
SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 9
Input Buf: Language Event: begin tran

update xttrace2
set fname='raveesh'
where city='cuttack'

waitfor delay '00:00:30'

update xttrace1
set fname='ashwani'
where city='moradabad'

commit tran
Requested by: 
ResType:LockOwner Stype:'OR'Xdes:0x0B1AD828 Mode: U SPID:56 BatchID:0 ECID:0 TaskProxy:(0x1895445C) Value:0x136958c0 Cost:(0/268)
NULL
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x0B1AD0F8 Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x1987245C) Value:0x13695f80 Cost:(0/268)

Now you can see that it provides the node base information along with victim information in the last.

Finally, don’t forget to turnoff the trace flag 1204 here during this test scenario.

use master
go
DBCC TRACEOFF(1204,-1)
Go

PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook