Hi ,

Can anyone explain about below deadlock information. in this below information which one is deadlock victim.

Deadlock encountered .... Printing deadlock information

2015-02-26 12:22:49.64 spid4     
2015-02-26 12:22:49.64 spid4     Wait-for graph
2015-02-26 12:22:49.64 spid4     
2015-02-26 12:22:49.64 spid4     Node:1
2015-02-26 12:22:49.64 spid4     PAG: 7:1:674984                CleanCnt:1 Mode: X Flags: 0x2
2015-02-26 12:22:49.64 spid4      Grant List 3::
2015-02-26 12:22:49.64 spid4        Owner:0x5bb87e00 Mode: X        Flg:0x0 Ref:7 Life:02000000 SPID:147 ECID:0
2015-02-26 12:22:49.64 spid4        SPID: 147 ECID: 0 Statement Type: UPDATE Line #: 212
2015-02-26 12:22:49.64 spid4        Input Buf: Language Event: EXEC Tax_taskprocess
2015-02-26 12:22:49.64 spid4      Requested By:
2015-02-26 12:22:49.64 spid4        ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0 Ec:(0x4221B9B8) Value:0x53bb9140 Cost:(0/464)
2015-02-26 12:22:49.64 spid4     
2015-02-26 12:22:49.64 spid4     Node:2
2015-02-26 12:22:49.64 spid4     PAG: 7:1:403375                CleanCnt:1 Mode: U Flags: 0x2
2015-02-26 12:22:49.64 spid4      Grant List 2::
2015-02-26 12:22:49.64 spid4        Owner:0x293a88e0 Mode: U        Flg:0x0 Ref:1 Life:00000000 SPID:71 ECID:0
2015-02-26 12:22:49.64 spid4        SPID: 71 ECID: 0 Statement Type: UPDATE Line #: 194
2015-02-26 12:22:49.64 spid4        Input Buf: RPC Event: sp_testcaselilly;1
2015-02-26 12:22:49.64 spid4      Requested By:
2015-02-26 12:22:49.64 spid4        ResType:LockOwner Stype:'OR' Mode: IU SPID:147 ECID:0 Ec:(0x33BA55B0) Value:0x571b92a0 Cost:(0/E55CC)
2015-02-26 12:22:49.64 spid4     Victim Resource Owner:
2015-02-26 12:22:49.64 spid4      ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0 Ec:(0x4221B9B8) Value:0x53bb9140 Cost:(0/464)

Check out the below lines:

2015-02-26 12:22:49.64 spid4     Victim Resource Owner:
2015-02-26 12:22:49.64 spid4      ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0 Ec:(0x4221B9B8) 

When you look at SPID71, you can see the below:

2015-02-26 12:22:49.64 spid4        SPID: 71 ECID: 0 Statement Type: UPDATE Line #: 194
2015-02-26 12:22:49.64 spid4        Input Buf: RPC Event: sp_testcaselilly;1

Hope, the above is clear...

hi Sreenivas,

SQL Server chose SPID:71 as victim and terminated it. For explanation see the link below

http://colleenmorrow.com/2011/03/01/deadlocks-and-trace-flag-1204/

Hope this helps

Many Thanks

Bhanu

• Marked as answer by Sreenivas DBA 6 hours 5 minutes ago

Please post complete deadlock graph so that we can comment about both victim and involved process in deadlock. I dont think you can draw much inference from  what you have posted

Hi Shanky,

It is SQLServer 2000. There is no event for deadlock graph. i just enabled DBCC TRACEON(1204, -1).

So that i got this information in error log.

Didnt you get the info that you are looking for from my last post? For any clarification, please let us know.

Hi Shanky,

It is SQLServer 2000. There is no event for deadlock graph. i just enabled DBCC TRACEON(1204, -1).

So that i got this information in error log.

Well you did not put in question this is deadlock graph of SQL Server 2000. Anyways you can refer what Latheesh posted

Thanks Bahnu . This is link is very helful for me

Thank you Latheesh

Hi Latheesh,

I need small information.

I am running sqlprofiler in sql2000, i selected events  Deadlock chain, Lock:Deadlock, RPC:Completed, SP:Completed, SP:STMT Completed, SQL:Batch Completed, SQL:STMT Completed,

Profiler is captured all the information like below

Lock:Deadlock chain: Deadlock Chain SPID-72

Lock:Deadlock chain: Deadlock Chain SPID-147

Lock:Deadlock :       SPID 72

SP:STMT Completed        ........                                        

In this which spid 147 is showing three different sp's in text data.

which one i need to confirm that belongs to spid 147 and spid 72..

Can you please explain profiler deadlock information also.

• Edited by Sreenivas DBA 5 hours 12 minutes ago

Run this:

DBCC TRACEON(3604)
DBCC PAGE(7, 1, 674984, 3)
DBCC PAGE(7, 1, 403375, 3)

This output includes an object id. Run SELECT object_name() on these object id. This wlil give yout the table(s) the clash occurred. The output from DBCC PAGE should also tell you which indexes they are quarreling about.

Or just look at the line number in the two stored procedures. And you will have to read the source code to understand why this deadlock occurred.