How can we help you today? How can we help you today?

How to stop SQL Response being "Blocker", causing Deadlock?

SQL Response was identified as the "Blocker" for my scheduled "Shrink DB" Job - so my job was nixed... I marked the SR command in blue below...

I don't want Jobs nixed due to a monitoring tool's activity - that not kosher...

How do we stop this, please?

Technical Information from the incident:
Deadlock encountered .... Printing deadlock information

Wait-for graph

Node:1
TAB: 12:1109578991 [] CleanCnt:3 Mode: IX Flags: 0x0
Grant List 3::
Owner:0x1b95af00 Mode: IX Flg:0x0 Ref:1 Life:00000000 SPID:87 ECID:0
SPID: 87 ECID: 0 Statement Type: DBCC Line #: 2
Input Buf: Language Event:
DBCC SHRINKDATABASE(N'PurposeDrivenBOS' )
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:57 ECID:0 Ec:(0x460F1588) Value:0x54750380 Cost:(0/0)

Node:2
TAB: 12:1109578991 [] CleanCnt:3 Mode: IX Flags: 0x0
Wait List:
Owner:0x54750380 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:57 ECID:0
SPID: 57 ECID: 0 Statement Type: DBCC Line #: 1
Input Buf: Language Event: select GETDATE(); DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, FAST
Requested By:
ResType:LockOwner Stype:'OR' Mode: IX SPID:87 ECID:0 Ec:(0x73B7F600) Value:0x1ae3e100 Cost:(1/0)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: IX SPID:87 ECID:0 Ec:(0x73B7F600) Value:0x1ae3e100 Cost:(1/0)
PDinCA
0

Comments

1 comment

Sort by
  • Nigel Morse
    Thanks for reporting this. We're going to switch to using a query that won't cause locks on the indexes which will hopefully fix it for final release
    Nigel Morse
    0

Add comment

Please sign in to leave a comment.