September 11, 2013 at 2:26 am
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.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply