Deadlock trouble shooting

Hi all, I have a rather annoying problem finding out what causes my deadlocks.
I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
scheduled tasks and perhaps 20 databases. I have read a couple of articles
regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
and 3605. I am trying to follow the steps of this article in order to
pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
Furthermore I have used the sqldiag utility as well as profiler and gotten
the outputfile sqldiag.txt and I am looking at it with the help of the
article:
Here are two examples of deadlocks from the sqldiag.txt:
--
2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
2005-03-30 11:29:16.19 spid4 Target Resource Owner:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
verifying cycle
2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4
Deadlock encountered ... Printing deadlock information
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Wait-for graph
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Node:1
2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
Mode: X Flags: 0x2
2005-03-30 11:29:16.19 spid4 Wait List:
2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
Ref:1 Life:00000000 SPID:90 ECID:0
2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
move_vcpu_dtl_from_spots @.days2copy = '1'
2005-03-30 11:29:16.19 spid4 Requested By:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Node:2
2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
Mode: X Flags: 0x2
2005-03-30 11:29:16.19 spid4 Grant List 1::
2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:88 ECID:0
2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
import_ssnc_dtl
2005-03-30 11:29:16.19 spid4 Requested By:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Node:3
2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
Mode: X Flags: 0x2
2005-03-30 11:29:16.19 spid4 Grant List 0::
2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:86 ECID:0
2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
import_pdc_sweden
2005-03-30 11:29:16.19 spid4 Requested By:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
was found.
2005-03-30 11:29:16.19 spid4 --
2005-03-30 11:29:21.19 spid4 --
2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
2005-03-30 11:29:21.19 spid4 Target Resource Owner:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
verifying cycle
2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4
Deadlock encountered ... Printing deadlock information
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Wait-for graph
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Node:1
2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
Mode: X Flags: 0x2
2005-03-30 11:29:21.19 spid4 Grant List 1::
2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:88 ECID:0
2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
import_ssnc_dtl
2005-03-30 11:29:21.19 spid4 Requested By:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Node:2
2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
Mode: X Flags: 0x2
2005-03-30 11:29:21.19 spid4 Grant List 0::
2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:86 ECID:0
2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
import_pdc_sweden
2005-03-30 11:29:21.19 spid4 Requested By:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
was found.
2005-03-30 11:29:21.19 spid4
--
Now as I understand it there should also be a value named "Key XXXX"
somewhere after the "Grant List" but I can not find it. I guess , correct me
if I am wrong, I need that value in order to take the trouble shooting
further... So this is as far as I get. Could someone please give me some
hints and pointers of what I need to do next'
Thanks on forehand!!
--
Alex
--
Computer Science StudentAlexandre geia ,
I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
your code regarding this line and try to find any loops or any other issue.
Also you can delete and create your indexes
Let me know if you need anything else
Andreas
"Alexander Simeonidis" wrote:
> Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> scheduled tasks and perhaps 20 databases. I have read a couple of articles
> regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> and 3605. I am trying to follow the steps of this article in order to
> pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> Furthermore I have used the sqldiag utility as well as profiler and gotten
> the outputfile sqldiag.txt and I am looking at it with the help of the
> article:
> Here are two examples of deadlocks from the sqldiag.txt:
> --
> 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> verifying cycle
> 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4
> Deadlock encountered ... Printing deadlock information
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Wait-for graph
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Node:1
> 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> Mode: X Flags: 0x2
> 2005-03-30 11:29:16.19 spid4 Wait List:
> 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> Ref:1 Life:00000000 SPID:90 ECID:0
> 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> move_vcpu_dtl_from_spots @.days2copy = '1'
> 2005-03-30 11:29:16.19 spid4 Requested By:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Node:2
> 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> Mode: X Flags: 0x2
> 2005-03-30 11:29:16.19 spid4 Grant List 1::
> 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:88 ECID:0
> 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> import_ssnc_dtl
> 2005-03-30 11:29:16.19 spid4 Requested By:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Node:3
> 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> Mode: X Flags: 0x2
> 2005-03-30 11:29:16.19 spid4 Grant List 0::
> 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:86 ECID:0
> 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> import_pdc_sweden
> 2005-03-30 11:29:16.19 spid4 Requested By:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> was found.
> 2005-03-30 11:29:16.19 spid4 --
> 2005-03-30 11:29:21.19 spid4 --
> 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> verifying cycle
> 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4
> Deadlock encountered ... Printing deadlock information
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Wait-for graph
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Node:1
> 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> Mode: X Flags: 0x2
> 2005-03-30 11:29:21.19 spid4 Grant List 1::
> 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:88 ECID:0
> 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> import_ssnc_dtl
> 2005-03-30 11:29:21.19 spid4 Requested By:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Node:2
> 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> Mode: X Flags: 0x2
> 2005-03-30 11:29:21.19 spid4 Grant List 0::
> 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:86 ECID:0
> 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> import_pdc_sweden
> 2005-03-30 11:29:21.19 spid4 Requested By:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> was found.
> 2005-03-30 11:29:21.19 spid4
> --
> Now as I understand it there should also be a value named "Key XXXX"
> somewhere after the "Grant List" but I can not find it. I guess , correct me
> if I am wrong, I need that value in order to take the trouble shooting
> further... So this is as far as I get. Could someone please give me some
> hints and pointers of what I need to do next'
> Thanks on forehand!!
>
> --
> Alex
> --
> Computer Science Student|||Geia Andrea :) kai efcharisto!
I am afraid the problem is not that simple. I did as you said and checked my
three
stored procedures, none had anything in common on line #50.
One had a print cmd,
the other an exec cmd and the third
an if stmnt. And these cmds had nothing in common i.e writing to the same
table etc.. So I am still looking for pointers of how to take my
troubleshooting further.
Thank you for your answer...
"Andreas Mavrogenis" wrote:
> Alexandre geia ,
> I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> your code regarding this line and try to find any loops or any other issue.
> Also you can delete and create your indexes
> Let me know if you need anything else
> Andreas
>
> "Alexander Simeonidis" wrote:
> > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > and 3605. I am trying to follow the steps of this article in order to
> > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > the outputfile sqldiag.txt and I am looking at it with the help of the
> > article:
> > Here are two examples of deadlocks from the sqldiag.txt:
> > --
> > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> >
> > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > verifying cycle
> > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4
> > Deadlock encountered ... Printing deadlock information
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Node:1
> > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:16.19 spid4 Wait List:
> > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > Ref:1 Life:00000000 SPID:90 ECID:0
> > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > move_vcpu_dtl_from_spots @.days2copy = '1'
> >
> > 2005-03-30 11:29:16.19 spid4 Requested By:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Node:2
> > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:88 ECID:0
> > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > import_ssnc_dtl
> > 2005-03-30 11:29:16.19 spid4 Requested By:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Node:3
> > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:86 ECID:0
> > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > import_pdc_sweden
> > 2005-03-30 11:29:16.19 spid4 Requested By:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > was found.
> > 2005-03-30 11:29:16.19 spid4 --
> >
> > 2005-03-30 11:29:21.19 spid4 --
> > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> >
> > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > verifying cycle
> > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4
> > Deadlock encountered ... Printing deadlock information
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Node:1
> > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:88 ECID:0
> > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > import_ssnc_dtl
> > 2005-03-30 11:29:21.19 spid4 Requested By:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Node:2
> > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:86 ECID:0
> > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > import_pdc_sweden
> > 2005-03-30 11:29:21.19 spid4 Requested By:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > was found.
> > 2005-03-30 11:29:21.19 spid4
> > --
> >
> > Now as I understand it there should also be a value named "Key XXXX"
> > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > if I am wrong, I need that value in order to take the trouble shooting
> > further... So this is as far as I get. Could someone please give me some
> > hints and pointers of what I need to do next'
> >
> > Thanks on forehand!!
> >
> >
> > --
> > Alex
> > --
> > Computer Science Student|||Geia,
Did you droped your indexes ?
Andreas
"Alexander Simeonidis" wrote:
> Geia Andrea :) kai efcharisto!
> I am afraid the problem is not that simple. I did as you said and checked my
> three
> stored procedures, none had anything in common on line #50.
> One had a print cmd,
> the other an exec cmd and the third
> an if stmnt. And these cmds had nothing in common i.e writing to the same
> table etc.. So I am still looking for pointers of how to take my
> troubleshooting further.
> Thank you for your answer...
> "Andreas Mavrogenis" wrote:
> > Alexandre geia ,
> >
> > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > your code regarding this line and try to find any loops or any other issue.
> >
> > Also you can delete and create your indexes
> >
> > Let me know if you need anything else
> > Andreas
> >
> >
> > "Alexander Simeonidis" wrote:
> >
> > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > and 3605. I am trying to follow the steps of this article in order to
> > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > article:
> > > Here are two examples of deadlocks from the sqldiag.txt:
> > > --
> > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > >
> > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > verifying cycle
> > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4
> > > Deadlock encountered ... Printing deadlock information
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > >
> > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > import_ssnc_dtl
> > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > import_pdc_sweden
> > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > was found.
> > > 2005-03-30 11:29:16.19 spid4 --
> > >
> > > 2005-03-30 11:29:21.19 spid4 --
> > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > >
> > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > verifying cycle
> > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4
> > > Deadlock encountered ... Printing deadlock information
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > import_ssnc_dtl
> > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > import_pdc_sweden
> > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > was found.
> > > 2005-03-30 11:29:21.19 spid4
> > > --
> > >
> > > Now as I understand it there should also be a value named "Key XXXX"
> > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > if I am wrong, I need that value in order to take the trouble shooting
> > > further... So this is as far as I get. Could someone please give me some
> > > hints and pointers of what I need to do next'
> > >
> > > Thanks on forehand!!
> > >
> > >
> > > --
> > > Alex
> > > --
> > > Computer Science Student|||Hi, do you mean dropping all my indexes on the tables involved?
I can not drop the indexes on my tables because the only index I have is the
PK.
To explain the flow of how my stored procedures work:
[Raw data table] -> [tmp-table ] -> [data-table]
The raw data tables contains no keys whatsoever and they are deleted after
each moving of data to the tmp tables. The tmp tables are also deleted after
each move of the data to the data tables. And they have no indexes or keys
either.
So the only table that have indexes are the datatables and for them the
index is the PK. But the PK consists of 5-6 different columns and the amount
of data in the table is about half of million rows.
The problems with deadlocks most often arises when I try to move data from
the raw data tables to the tmp tables.
Thanks a lot for your help and patience...
"Andreas Mavrogenis" wrote:
> Geia,
> Did you droped your indexes ?
> Andreas
> "Alexander Simeonidis" wrote:
> > Geia Andrea :) kai efcharisto!
> > I am afraid the problem is not that simple. I did as you said and checked my
> > three
> > stored procedures, none had anything in common on line #50.
> > One had a print cmd,
> > the other an exec cmd and the third
> > an if stmnt. And these cmds had nothing in common i.e writing to the same
> > table etc.. So I am still looking for pointers of how to take my
> > troubleshooting further.
> > Thank you for your answer...
> >
> > "Andreas Mavrogenis" wrote:
> >
> > > Alexandre geia ,
> > >
> > > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > > your code regarding this line and try to find any loops or any other issue.
> > >
> > > Also you can delete and create your indexes
> > >
> > > Let me know if you need anything else
> > > Andreas
> > >
> > >
> > > "Alexander Simeonidis" wrote:
> > >
> > > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > > and 3605. I am trying to follow the steps of this article in order to
> > > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > > article:
> > > > Here are two examples of deadlocks from the sqldiag.txt:
> > > > --
> > > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:16.19 spid4 --
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 --
> > > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:21.19 spid4
> > > > --
> > > >
> > > > Now as I understand it there should also be a value named "Key XXXX"
> > > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > > if I am wrong, I need that value in order to take the trouble shooting
> > > > further... So this is as far as I get. Could someone please give me some
> > > > hints and pointers of what I need to do next'
> > > >
> > > > Thanks on forehand!!
> > > >
> > > >
> > > > --
> > > > Alex
> > > > --
> > > > Computer Science Student|||Alexandre gria kai pali,
Look, try to delete * from tmp before raw data insertion. Try the insertion
with insert statement rather that the update. I saw the update statement in
the error that you posted.
Also, try to put indexes on you tables if your app allows
Try that and let me know,
Andreas
"Alexander Simeonidis" wrote:
> Hi, do you mean dropping all my indexes on the tables involved?
> I can not drop the indexes on my tables because the only index I have is the
> PK.
> To explain the flow of how my stored procedures work:
> [Raw data table] -> [tmp-table ] -> [data-table]
> The raw data tables contains no keys whatsoever and they are deleted after
> each moving of data to the tmp tables. The tmp tables are also deleted after
> each move of the data to the data tables. And they have no indexes or keys
> either.
> So the only table that have indexes are the datatables and for them the
> index is the PK. But the PK consists of 5-6 different columns and the amount
> of data in the table is about half of million rows.
> The problems with deadlocks most often arises when I try to move data from
> the raw data tables to the tmp tables.
> Thanks a lot for your help and patience...
>
> "Andreas Mavrogenis" wrote:
> > Geia,
> >
> > Did you droped your indexes ?
> > Andreas
> >
> > "Alexander Simeonidis" wrote:
> >
> > > Geia Andrea :) kai efcharisto!
> > > I am afraid the problem is not that simple. I did as you said and checked my
> > > three
> > > stored procedures, none had anything in common on line #50.
> > > One had a print cmd,
> > > the other an exec cmd and the third
> > > an if stmnt. And these cmds had nothing in common i.e writing to the same
> > > table etc.. So I am still looking for pointers of how to take my
> > > troubleshooting further.
> > > Thank you for your answer...
> > >
> > > "Andreas Mavrogenis" wrote:
> > >
> > > > Alexandre geia ,
> > > >
> > > > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > > > your code regarding this line and try to find any loops or any other issue.
> > > >
> > > > Also you can delete and create your indexes
> > > >
> > > > Let me know if you need anything else
> > > > Andreas
> > > >
> > > >
> > > > "Alexander Simeonidis" wrote:
> > > >
> > > > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > > > and 3605. I am trying to follow the steps of this article in order to
> > > > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > > > article:
> > > > > Here are two examples of deadlocks from the sqldiag.txt:
> > > > > --
> > > > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > > > >
> > > > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > > > verifying cycle
> > > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > Deadlock encountered ... Printing deadlock information
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > > > >
> > > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > > import_ssnc_dtl
> > > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > > import_pdc_sweden
> > > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > > > was found.
> > > > > 2005-03-30 11:29:16.19 spid4 --
> > > > >
> > > > > 2005-03-30 11:29:21.19 spid4 --
> > > > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > > > >
> > > > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > > > verifying cycle
> > > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > Deadlock encountered ... Printing deadlock information
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > > import_ssnc_dtl
> > > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > > import_pdc_sweden
> > > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > > > was found.
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > --
> > > > >
> > > > > Now as I understand it there should also be a value named "Key XXXX"
> > > > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > > > if I am wrong, I need that value in order to take the trouble shooting
> > > > > further... So this is as far as I get. Could someone please give me some
> > > > > hints and pointers of what I need to do next'
> > > > >
> > > > > Thanks on forehand!!
> > > > >
> > > > >
> > > > > --
> > > > > Alex
> > > > > --
> > > > > Computer Science Student|||See that article also,
http://support.microsoft.com/default.aspx?scid=kb;en-us;821537
CU
Andreas
"Andreas Mavrogenis" wrote:
> Geia,
> Did you droped your indexes ?
> Andreas
> "Alexander Simeonidis" wrote:
> > Geia Andrea :) kai efcharisto!
> > I am afraid the problem is not that simple. I did as you said and checked my
> > three
> > stored procedures, none had anything in common on line #50.
> > One had a print cmd,
> > the other an exec cmd and the third
> > an if stmnt. And these cmds had nothing in common i.e writing to the same
> > table etc.. So I am still looking for pointers of how to take my
> > troubleshooting further.
> > Thank you for your answer...
> >
> > "Andreas Mavrogenis" wrote:
> >
> > > Alexandre geia ,
> > >
> > > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > > your code regarding this line and try to find any loops or any other issue.
> > >
> > > Also you can delete and create your indexes
> > >
> > > Let me know if you need anything else
> > > Andreas
> > >
> > >
> > > "Alexander Simeonidis" wrote:
> > >
> > > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > > and 3605. I am trying to follow the steps of this article in order to
> > > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > > article:
> > > > Here are two examples of deadlocks from the sqldiag.txt:
> > > > --
> > > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:16.19 spid4 --
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 --
> > > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:21.19 spid4
> > > > --
> > > >
> > > > Now as I understand it there should also be a value named "Key XXXX"
> > > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > > if I am wrong, I need that value in order to take the trouble shooting
> > > > further... So this is as far as I get. Could someone please give me some
> > > > hints and pointers of what I need to do next'
> > > >
> > > > Thanks on forehand!!
> > > >
> > > >
> > > > --
> > > > Alex
> > > > --
> > > > Computer Science Student