December 29, 2014 at 12:56 am
Please help with DEADLOCK info,i am unable to find anything here
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
Port: 0x00000000B532C560 Xid Slot: 11, Wait Slot: 2, Task: 0x000000017A442748, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:31 TaskProxy:(0x00000001553EB0A0) Value:0x7a442748 Cost:(20/0)
SPID: 24 ECID: 31 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:2
Port: 0x00000000A949C490 Xid Slot: 15, Wait Slot: 1, Task: 0x00000000A8974088, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:27 TaskProxy:(0x00000001553EAF20) Value:0xa8974088 Cost:(20/0)
NULL
Node:3
Port: 0x000000013AC79760 Xid Slot: 2, Wait Slot: 15, Task: 0x000000008E99FB88, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:23 TaskProxy:(0x00000001553EADA0) Value:0x8e99fb88 Cost:(0/10000)
NULL
Node:4
Port: 0x000000015B7DE2E0 Xid Slot: 1, Wait Slot: 11, Task: 0x000000013B3AB708, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:18 TaskProxy:(0x00000001553EABC0) Value:0x3b3ab708 Cost:(0/10000)
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
Port: 0x00000000A8B43280 Xid Slot: 8, Wait Slot: 3, Task: 0x0000000119F6F288, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:30 TaskProxy:(0x00000001553EB040) Value:0x19f6f288 Cost:(20/0)
SPID: 24 ECID: 30 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:2
Port: 0x00000000A949C490 Xid Slot: 15, Wait Slot: 1, Task: 0x00000000A8974088, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:27 TaskProxy:(0x00000001553EAF20) Value:0xa8974088 Cost:(20/0)
NULL
Node:3
Port: 0x0000000113432F40 Xid Slot: 3, Wait Slot: 15, Task: 0x000000017A443288, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:22 TaskProxy:(0x00000001553EAD40) Value:0x7a443288 Cost:(0/10000)
SPID: 24 ECID: 22 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:4
Port: 0x000000009645D100 Xid Slot: 1, Wait Slot: 8, Task: 0x000000013B3AB708, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:18 TaskProxy:(0x00000001553EABC0) Value:0x3b3ab708 Cost:(0/10000)
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
Port: 0x000000010D301830 Xid Slot: 12, Wait Slot: 6, Task: 0x00000000E9A7E988, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:26 TaskProxy:(0x00000001553EAEC0) Value:0xe9a7e988 Cost:(20/0)
SPID: 24 ECID: 26 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:2
Port: 0x00000000A949C490 Xid Slot: 15, Wait Slot: 1, Task: 0x00000000A8974088, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:27 TaskProxy:(0x00000001553EAF20) Value:0xa8974088 Cost:(20/0)
NULL
Node:3
Port: 0x000000015E58AC80 Xid Slot: 6, Wait Slot: 15, Task: 0x00000000FDBAABC8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:21 TaskProxy:(0x00000001553EACE0) Value:0xfdbaabc8 Cost:(0/10000)
SPID: 24 ECID: 21 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:4
Port: 0x00000001637DAC40 Xid Slot: 1, Wait Slot: 12, Task: 0x000000013B3AB708, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:18 TaskProxy:(0x00000001553EABC0) Value:0x3b3ab708 Cost:(0/10000)
Using 'dbghelp.dll' version '4.0.5'
Using 'dbghelp.dll' version '4.0.5'
Using 'dbghelp.dll' version '4.0.5'
Using 'dbghelp.dll' version '4.0.5'
Using 'dbghelp.dll' version '4.0.5'
Using 'dbghelp.dll' version '4.0.5'
Using 'dbghelp.dll' version '4.0.5'
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
Port: 0x00000000F676D590 Xid Slot: 13, Wait Slot: 1, Task: 0x00000000B58A1288, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:27 BatchID:0 ECID:27 TaskProxy:(0x000000017A0CCF20) Value:0xb58a1288 Cost:(20/0)
SPID: 27 ECID: 27 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:2
Port: 0x000000015D9394A0 Xid Slot: 11, Wait Slot: 2, Task: 0x00000000E9A7E748, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:27 BatchID:0 ECID:29 TaskProxy:(0x000000017A0CCFE0) Value:0xe9a7e748 Cost:(20/0)
SPID: 27 ECID: 29 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:3
Port: 0x000000013D264210 Xid Slot: 2, Wait Slot: 13, Task: 0x000000013B3ABDC8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:27 BatchID:0 ECID:18 TaskProxy:(0x000000017A0CCBC0) Value:0x3b3abdc8 Cost:(0/10000)
SPID: 27 ECID: 18 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:4
Port: 0x00000000874BAA30 Xid Slot: 1, Wait Slot: 11, Task: 0x000000008E99F288, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:27 BatchID:0 ECID:17 TaskProxy:(0x000000017A0CCB60) Value:0x8e99f288 Cost:(0/10000)
SPID: 27 ECID: 17 Statement Type: INSERT Line #: 17
Input Buf: No Event:
Using 'dbghelp.dll' version '4.0.5'
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 29, 2014 at 2:01 am
To start, turn traceflag 1204 off and use the 'newer' one that was introduced in SQL 2005 and which produces better deadlock graphs with more details. 1204 often doesn't produce enough information to debug.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2014 at 12:09 pm
Or, more specifically, run these commands:
DBCC TRACEOFF ( 1204, -1 )
DBCC TRACEON ( 1222, -1 )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 29, 2014 at 10:13 pm
thanks scott and gail
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 30, 2014 at 8:51 am
Btw, either add 1222 setting directly to the start-up flags or add it to a start-up proc. Otherwise you'll lose that flag setting when SQL stops and restarts.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply