5

I have a 3 node cluster and the 2 secondaries initially went into SUSPECT mode due to a deadlock. I tried to resume both and only one caught up while the other is continuously deadlocking. I have to confess that troubleshooting deadlocks is not my main strength and with the little information provided by the xml I can't figure out what is going on. This DB has 4 full text indexes and the pageID reported in the deadlock graph has the m_type 10 = IAM page ? Is there any way I can get the REDO resumed or do I need to drop the replica and set it up again ? I can't get my head around why did the other node catch up if the log sequence is the same ?

Thanks a lot

<deadlock>
  <victim-list>
    <victimProcess id="process23bb0c8" />
  </victim-list>
  <process-list>
    <process id="process23bb0c8" taskpriority="-20" logused="0" waitresource="PAGE: 21:1:47934 " 
    XDES="0x407dc8ff0" lockMode="X" schedulerid="2" kpid="4604" status="background" spid="66" sbid="0" ecid="0" priority="0" trancount="0">
      <executionStack />
      <inputbuf />
    </process>
  </process-list>
  <resource-list>
    <pagelock fileid="1" pageid="47934" dbid="21" subresource="FULL" 
    objectname="DB.sys.fulltext_index_1_69575286" id="lock417f18100" mode="IX" associatedObjectId="72057594044219392">
      <owner-list>
        <owner id="process23bb0c8" mode="IX" />
        <owner id="process23bb0c8" mode="IX" />
        <owner id="process23bb0c8" mode="IX" />
        <owner id="process23bb0c8" mode="IX" />
        <owner id="process23bb0c8" mode="X" requestType="wait" />
      </owner-list>
      <waiter-list>
        <waiter id="process23bb0c8" mode="X" requestType="wait" />
      </waiter-list>
    </pagelock>
  </resource-list>
</deadlock>
  • Have you attempted to run RESUME on that database? – Nic Aug 25 '17 at 14:44
  • I did indeed . Initially both replicas deadlocked and I was able to resume on the third Async node but not so lucky with the second synchronous node. I've tried multiple times to resume but always ends up on a deadlock . – MrKudz Aug 25 '17 at 14:45
  • What version of sql server r u running ? Is it patched with latest SP+CU ? Any memory dumps created ? – Kin Aug 25 '17 at 14:49
  • Microsoft SQL Server 2012 (SP3-CU1) (KB3123299) - 11.0.6518.0 (X64) No memory dumps in any of the 3 nodes . – MrKudz Aug 25 '17 at 14:54
  • Patch to latest CU (CU10) or just remove and add the db in AG. Has been some good fixes for AGs. e.g. support.microsoft.com/en-us/help/3213703/… – Kin Aug 25 '17 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.