none
Management Data Warehouse Data Collector upload job deadlocks

    Question

  • Hello,

    I have several SQL Server 2008 instances with Management Data Warehouse enabled with the default settings. On all monitored servers - without exception I am seeing occasional (1-5 times a day) failures of the "collection_set_2_upload" job and the "collection_set_3_upload" deadlocking of the "collection_set_2_upload_purge_logs" and the "collection_set_3_upload_purge_logs" job steps.

    It doesn't happen often enough that the log is not cleared but it does clog my system_heath ring buffer with deadlock noise.

    I suspect OPTION(MAXDOP 1) on the queries of the called stored proc: [msdb].[dbo].[sp_syscollector_purge_collection_logs] would end the deadlock but wish to avoid mucking around with system created objects. Can someone suggest a suitable work around?

    here is a sample deadlock from the ring buffer

    <deadlock-list>
      <deadlock>
        <victim-list>
          <victimProcess id="process3c9d4c8" />
        </victim-list>
        <process-list>
          <process id="process3c9d4c8" taskpriority="0" logused="0" waitresource="PAGE: 4:1:2787" waittime="519" ownerId="1218738410" transactionname="DELETE" lasttranstarted="2009-10-27T02:45:00.490" XDES="0x5e1f94080" lockMode="U" schedulerid="4" kpid="45568" status="suspended" spid="82" sbid="0" ecid="14" priority="0" trancount="0" lastbatchstarted="2009-10-27T02:45:00.477" lastbatchcompleted="2009-10-27T02:45:00.477" clientapp="SQLAgent - TSQL JobStep (Job 0xBC776A29D911AB48B7ACC878B1101642 : Step 1)" hostname="BIANCA" hostpid="5896" isolationlevel="read committed (2)" xactid="1218738410" currentdb="4" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="" line="31" stmtstart="2152" stmtend="2720" sqlhandle="0x03000400978cf46fe4de3301789c00000100000000000000" />
              <frame procname="" line="2" stmtstart="28" sqlhandle="0x0100040034b4980c20992bce050000000000000000000000" />
            </executionStack>
            <inputbuf />
          </process>
          <process id="process4d17708" taskpriority="0" logused="0" waitresource="PAGE: 4:1:12607" waittime="516" ownerId="1218738410" transactionname="DELETE" lasttranstarted="2009-10-27T02:45:00.490" XDES="0x489a70450" lockMode="U" schedulerid="13" kpid="47492" status="suspended" spid="82" sbid="0" ecid="11" priority="0" trancount="0" lastbatchstarted="2009-10-27T02:45:00.477" lastbatchcompleted="2009-10-27T02:45:00.477" clientapp="SQLAgent - TSQL JobStep (Job 0xBC776A29D911AB48B7ACC878B1101642 : Step 1)" hostname="BIANCA" hostpid="5896" isolationlevel="read committed (2)" xactid="1218738410" currentdb="4" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="" line="31" stmtstart="2152" stmtend="2720" sqlhandle="0x03000400978cf46fe4de3301789c00000100000000000000" />
              <frame procname="" line="2" stmtstart="28" sqlhandle="0x0100040034b4980c20992bce050000000000000000000000" />
            </executionStack>
            <inputbuf />
          </process>
          <process id="process3c83b88" taskpriority="0" logused="0" waitresource="PAGE: 4:1:12639" waittime="515" ownerId="1218738401" transactionname="DELETE" lasttranstarted="2009-10-27T02:45:00.487" XDES="0x81951b20" lockMode="U" schedulerid="3" kpid="14812" status="suspended" spid="80" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2009-10-27T02:45:00.477" lastbatchcompleted="2009-10-27T02:45:00.477" clientapp="SQLAgent - TSQL JobStep (Job 0xB41BC0494B37C9439332804C5D513A9A : Step 1)" hostname="BIANCA" hostpid="5896" isolationlevel="read committed (2)" xactid="1218738401" currentdb="4" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="" line="31" stmtstart="2152" stmtend="2720" sqlhandle="0x03000400978cf46fe4de3301789c00000100000000000000" />
              <frame procname="" line="2" stmtstart="28" sqlhandle="0x0100040034b4980c20992bce050000000000000000000000" />
            </executionStack>
            <inputbuf />
          </process>
          <process id="process3c9d708" taskpriority="0" logused="0" waitresource="PAGE: 4:1:6397" waittime="520" ownerId="1218738401" transactionname="DELETE" lasttranstarted="2009-10-27T02:45:00.487" XDES="0x80028d20" lockMode="U" schedulerid="4" kpid="45044" status="suspended" spid="80" sbid="0" ecid="12" priority="0" trancount="0" lastbatchstarted="2009-10-27T02:45:00.477" lastbatchcompleted="2009-10-27T02:45:00.477" clientapp="SQLAgent - TSQL JobStep (Job 0xB41BC0494B37C9439332804C5D513A9A : Step 1)" hostname="BIANCA" hostpid="5896" isolationlevel="read committed (2)" xactid="1218738401" currentdb="4" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="" line="31" stmtstart="2152" stmtend="2720" sqlhandle="0x03000400978cf46fe4de3301789c00000100000000000000" />
              <frame procname="" line="2" stmtstart="28" sqlhandle="0x0100040034b4980c20992bce050000000000000000000000" />
            </executionStack>
            <inputbuf />
          </process>
          <process id="process49ed048" taskpriority="0" logused="0" waitresource="PAGE: 4:1:30711" waittime="520" ownerId="1218738410" transactionname="DELETE" lasttranstarted="2009-10-27T02:45:00.490" XDES="0x23ddfba10" lockMode="U" schedulerid="12" kpid="48220" status="suspended" spid="82" sbid="0" ecid="7" priority="0" trancount="0" lastbatchstarted="2009-10-27T02:45:00.477" lastbatchcompleted="2009-10-27T02:45:00.477" clientapp="SQLAgent - TSQL JobStep (Job 0xBC776A29D911AB48B7ACC878B1101642 : Step 1)" hostname="BIANCA" hostpid="5896" isolationlevel="read committed (2)" xactid="1218738410" currentdb="4" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="" line="31" stmtstart="2152" stmtend="2720" sqlhandle="0x03000400978cf46fe4de3301789c00000100000000000000" />
              <frame procname="" line="2" stmtstart="28" sqlhandle="0x0100040034b4980c20992bce050000000000000000000000" />
            </executionStack>
            <inputbuf />
          </process>
          <process id="process44b6e08" taskpriority="0" logused="0" waitresource="PAGE: 4:1:6397" waittime="519" ownerId="1218738401" transactionname="DELETE" lasttranstarted="2009-10-27T02:45:00.487" XDES="0x80025d40" lockMode="U" schedulerid="5" kpid="48164" status="suspended" spid="80" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2009-10-27T02:45:00.477" lastbatchcompleted="2009-10-27T02:45:00.477" clientapp="SQLAgent - TSQL JobStep (Job 0xB41BC0494B37C9439332804C5D513A9A : Step 1)" hostname="BIANCA" hostpid="5896" isolationlevel="read committed (2)" xactid="1218738401" currentdb="4" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="" line="31" stmtstart="2152" stmtend="2720" sqlhandle="0x03000400978cf46fe4de3301789c00000100000000000000" />
              <frame procname="" line="2" stmtstart="28" sqlhandle="0x0100040034b4980c20992bce050000000000000000000000" />
            </executionStack>
            <inputbuf />
          </process>
          <process id="process49d2e08" taskpriority="0" logused="0" waitresource="PAGE: 4:1:12607" waittime="515" ownerId="1218738410" transactionname="DELETE" lasttranstarted="2009-10-27T02:45:00.490" XDES="0x1585b8cd0" lockMode="U" schedulerid="11" kpid="49248" status="suspended" spid="82" sbid="0" ecid="16" priority="0" trancount="0" lastbatchstarted="2009-10-27T02:45:00.477" lastbatchcompleted="2009-10-27T02:45:00.477" clientapp="SQLAgent - TSQL JobStep (Job 0xBC776A29D911AB48B7ACC878B1101642 : Step 1)" hostname="BIANCA" hostpid="5896" isolationlevel="read committed (2)" xactid="1218738410" currentdb="4" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="" line="31" stmtstart="2152" stmtend="2720" sqlhandle="0x03000400978cf46fe4de3301789c00000100000000000000" />
              <frame procname="" line="2" stmtstart="28" sqlhandle="0x0100040034b4980c20992bce050000000000000000000000" />
            </executionStack>
            <inputbuf />
          </process>
          <process id="process4d65dc8" taskpriority="0" logused="0" waitresource="PAGE: 4:1:30711" waittime="520" ownerId="1218738410" transactionname="DELETE" lasttranstarted="2009-10-27T02:45:00.490" XDES="0x407d56080" lockMode="U" schedulerid="16" kpid="48392" status="suspended" spid="82" sbid="0" ecid="12" priority="0" trancount="0" lastbatchstarted="2009-10-27T02:45:00.477" lastbatchcompleted="2009-10-27T02:45:00.477" clientapp="SQLAgent - TSQL JobStep (Job 0xBC776A29D911AB48B7ACC878B1101642 : Step 1)" hostname="BIANCA" hostpid="5896" isolationlevel="read committed (2)" xactid="1218738410" currentdb="4" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="" line="31" stmtstart="2152" stmtend="2720" sqlhandle="0x03000400978cf46fe4de3301789c00000100000000000000" />
              <frame procname="" line="2" stmtstart="28" sqlhandle="0x0100040034b4980c20992bce050000000000000000000000" />
            </executionStack>
            <inputbuf />
          </process>
          <process id="process4d4b4c8" taskpriority="0" logused="10000" waittime="485" schedulerid="15" kpid="49968" status="suspended" spid="82" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2009-10-27T02:45:00.477" lastbatchcompleted="2009-10-27T02:45:00.477" clientapp="SQLAgent - TSQL JobStep (Job 0xBC776A29D911AB48B7ACC878B1101642 : Step 1)" hostname="BIANCA" hostpid="5896" loginname="PINNACLESPORTS\sqlservice" isolationlevel="read committed (2)" xactid="1218738410" currentdb="4" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="" line="31" stmtstart="2152" stmtend="2720" sqlhandle="0x03000400978cf46fe4de3301789c00000100000000000000" />
              <frame procname="" line="2" stmtstart="28" sqlhandle="0x0100040034b4980c20992bce050000000000000000000000" />
            </executionStack>
            <inputbuf>

                EXEC [dbo].[sp_syscollector_purge_collection_logs]
                    </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <pagelock fileid="1" pageid="2787" dbid="4" objectname="" id="lock45231c600" mode="U" associatedObjectId="72057594047037440">
            <owner-list>
              <owner id="process44b6e08" mode="U" />
            </owner-list>
            <waiter-list>
              <waiter id="process3c9d4c8" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <pagelock fileid="1" pageid="12607" dbid="4" objectname="" id="lock12a21b580" mode="U" associatedObjectId="72057594047037440">
            <owner-list>
              <owner id="process3c83b88" mode="U" />
            </owner-list>
            <waiter-list>
              <waiter id="process4d17708" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <pagelock fileid="1" pageid="12639" dbid="4" objectname="" id="lock1f305b200" mode="U" associatedObjectId="72057594047037440">
            <owner-list>
              <owner id="process4d4b4c8" mode="U" />
            </owner-list>
            <waiter-list>
              <waiter id="process3c83b88" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <pagelock fileid="1" pageid="6397" dbid="4" objectname="" id="lock9547c900" mode="U" associatedObjectId="72057594047037440">
            <owner-list>
              <owner id="process4d4b4c8" mode="U" />
            </owner-list>
            <waiter-list>
              <waiter id="process3c9d708" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <pagelock fileid="1" pageid="30711" dbid="4" objectname="" id="lock915c3e80" mode="U" associatedObjectId="72057594047037440">
            <owner-list>
              <owner id="process3c83b88" mode="U" />
            </owner-list>
            <waiter-list>
              <waiter id="process49ed048" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <pagelock fileid="1" pageid="6397" dbid="4" objectname="" id="lock9547c900" mode="U" associatedObjectId="72057594047037440">
            <owner-list />
            <waiter-list>
              <waiter id="process44b6e08" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <pagelock fileid="1" pageid="12607" dbid="4" objectname="" id="lock12a21b580" mode="U" associatedObjectId="72057594047037440">
            <owner-list />
            <waiter-list>
              <waiter id="process49d2e08" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <pagelock fileid="1" pageid="30711" dbid="4" objectname="" id="lock915c3e80" mode="U" associatedObjectId="72057594047037440">
            <owner-list />
            <waiter-list>
              <waiter id="process4d65dc8" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <exchangeEvent id="Pipe159720e00" WaitType="e_waitPipeGetRow" nodeId="3">
            <owner-list>
              <owner id="process4d17708" />
              <owner id="process49ed048" />
              <owner id="process3c9d4c8" />
              <owner id="process4d65dc8" />
              <owner id="process49d2e08" />
            </owner-list>
            <waiter-list>
              <waiter id="process4d4b4c8" />
            </waiter-list>
          </exchangeEvent>
        </resource-list>
      </deadlock>
    </deadlock-list>
    Tuesday, October 27, 2009 2:11 PM

Answers

  • hey growling one
    As a workaround, If you set the collection_set_3_upload SQLAgent job to run a few minutes after the collection_set_2_upload job you can stop the deadlocking.
    Bill
    Tuesday, November 17, 2009 9:03 AM

All replies

  • Could you please report this issue at https://connect.microsoft.com/SQLServer/feedback 

    Thanks
    Sethu Srinivasan [MSFT]
    SQL Server
    http://blogs.msdn.com/sethus
    • Proposed as answer by Kalman TothEditor Friday, October 30, 2009 2:42 PM
    • Marked as answer by Kalman TothEditor Tuesday, November 10, 2009 10:15 AM
    • Unmarked as answer by bwunder Tuesday, November 17, 2009 9:04 AM
    Tuesday, October 27, 2009 5:39 PM
    Moderator
  • done
    Tuesday, October 27, 2009 6:18 PM
  • Hi bwunder,

    Can you share the feedback link to us, then others who have the same problem can see it.

    Thanks
    ChunSong Feng
    Microsoft Online Community Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, November 2, 2009 2:47 AM
    Moderator
  • Hi there does anyone know if there has been a fix for this?

    We are also getting dead locks

    EXEC [dbo].[sp_syscollector_purge_collection_logs]     

        inputbuf
       process id=processcf57048 taskpriority=0 logused=10000 waittime=4808 schedulerid=13 kpid=25128 status=suspended spid=197 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2009-11-17T11:45:00.120 lastbatchcompleted=2009-11-17T11:45:00.120 clientapp=SQLAgent - TSQL JobStep (Job 0x380E970CDC98B141BC02BDCAF7423222 : Step 1) hostname=XXXXXX hostpid=4068 loginname=XXXXXX isolationlevel=read committed (2) xactid=1102274275 currentdb=4 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
        executionStack
         frame procname=msdb.dbo.sp_syscollector_purge_collection_logs line=31 stmtstart=2152 stmtend=2720 sqlhandle=0x03000400978cf46f104e0d012a9c00000100000000000000
    DELETE FROM dbo.sysssislog
            FROM dbo.sysssislog AS s
            INNER JOIN dbo.syscollector_execution_log_internal AS l ON (l.package_execution_id = s.executionid)
            INNER JOIN #purged_log_ids AS i ON i.log_id = l.log_id
        -- Then delete the actual logs    
         frame procname=adhoc line=2 stmtstart=28 sqlhandle=0x0100040034b4980c400e903c010000000000000000000000
    EXEC [dbo].[sp_syscollector_purge_collection_logs]    
        inputbuf
                EXEC [dbo].[sp_syscollector_purge_collection_logs]
      resource-list
       pagelock fileid=1 pageid=66487 dbid=4 objectname=msdb.dbo.sysssislog id=lock332f43c00 mode=U associatedObjectId=72057594047037440
        owner-list
         owner id=process84db288 mode=U
        waiter-list

    Tuesday, November 17, 2009 6:01 AM
  • hey growling one
    As a workaround, If you set the collection_set_3_upload SQLAgent job to run a few minutes after the collection_set_2_upload job you can stop the deadlocking.
    Bill
    Tuesday, November 17, 2009 9:03 AM
  • I was able to resolve the job failure by changing the time of the job by a few minutes as Bill stated, but I am still seeing many a deadlocks.

    Did Microsoft ever address this problem and/or find resolution?

    Thanks
    Monday, January 18, 2010 8:59 PM