Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_MSsubscription_cleanup blocking sys.sp_MSadd_distribution_history Expand / Collapse
Author
Message
Posted Wednesday, September 11, 2013 2:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 175, Visits: 547
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 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>'

SELECT Text FROM sys.dm_exec_sql_text(0x0300ff7fba187e11aad52601a99e00000100000000000000)
-- sys.sp_MSadd_distribution_history
-- Line 319 : UPDATE MSdistribution_history

SELECT Text 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

SELECT Text FROM sys.dm_exec_sql_text(0x01000b0024da403098fe0c3b000000000000000000000000)
-- No rows returned.





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

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1493496
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse