sp_MSsubscription_cleanup blocking sys.sp_MSadd_distribution_history

  • Hi,

    Just getting started on understanding blocked processes and how to resolve them.

    Last night between 23:05 and 23:08 150 - 200 Blocked process reports were captured by profiler.

    How can I prevent sp_MSsubscription_cleanup from blocking sys.sp_MSadd_distribution_history?

    Following Jonathan Kehayias article[/url] on blocked process reports I have gathered the following information. (All the reports point to the same handles)

    DECLARE@XML XML

    SELECT@XML = '

    <blocked-process-report monitorLoop="1834546">

    <blocked-process>

    <process id="processfb0898" taskpriority="0" logused="0" waitresource="KEY: 11:72057594051297280 (b7023f4e732d)" waittime="88640" ownerId="1877351026" transactionname="UPDATE" lasttranstarted="2013-09-10T23:06:07.810" XDES="0x1e748250" lockMode="X" schedulerid="6" kpid="14024" status="suspended" spid="91" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2013-09-10T23:06:07.807" lastbatchcompleted="2013-09-10T23:05:06.740" clientapp="Replication Distribution History" hostname="Svr003" hostpid="4752" loginname="Domain\RepAccount" isolationlevel="read committed (2)" xactid="1877351026" currentdb="11" lockTimeout="4294967295" clientoption1="538968352" clientoption2="128024">

    <executionStack>

    <frame line="319" stmtstart="23820" stmtend="24938" sqlhandle="0x0300ff7fba187e11aad52601a99e00000100000000000000"/>

    </executionStack>

    <inputbuf>

    Proc [Database Id = 32767 Object Id = 293476538] </inputbuf>

    </process>

    </blocked-process>

    <blocking-process>

    <process status="running" spid="157" sbid="0" ecid="0" priority="-5" transcount="2" lastbatchstarted="2013-09-10T23:05:00.460" lastbatchcompleted="2013-09-10T23:05:00.460" clientapp="SQLAgent - TSQL JobStep (Job 0x7AF3443521934B48830F869CAE4DA477 : Step 1)" hostname="CLSvr003" hostpid="20476" loginname="Domain\SQLAccount" isolationlevel="read committed (2)" xactid="1877307652" currentdb="11" lockTimeout="4294967295" clientoption1="673186080" clientoption2="128024">

    <executionStack>

    <frame line="41" stmtstart="2394" stmtend="11138" sqlhandle="0x03000b00fb1c2229a6b9a600df9f00000100000000000000"/>

    <frame line="75" stmtstart="6280" stmtend="6406" sqlhandle="0x03000b0018d2e62dabb9a600df9f00000100000000000000"/>

    <frame line="1" sqlhandle="0x01000b0024da403098fe0c3b000000000000000000000000"/>

    </executionStack>

    <inputbuf>

    EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72 </inputbuf>

    </process>

    </blocking-process>

    </blocked-process-report>'

    SELECTText FROM sys.dm_exec_sql_text(0x0300ff7fba187e11aad52601a99e00000100000000000000)

    -- sys.sp_MSadd_distribution_history

    -- Line 319 : UPDATE MSdistribution_history

    SELECTText FROM sys.dm_exec_sql_text(0x03000b00fb1c2229a6b9a600df9f00000100000000000000)

    -- sp_MSsubscription_cleanup

    -- Line 41 : update MSsubscriptions (Multiple joins on MSdistribution_history)

    -- Line 75 : CASE with selects, no mention of MSdistribution_history

    SELECTText FROM sys.dm_exec_sql_text(0x01000b0024da403098fe0c3b000000000000000000000000)

    -- No rows returned.



    For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply