Blocking Issue

Question

Post reply

Blocking Issue

jdrinkwater

SSC-Addicted

Points: 461
More actions
April 27, 2012 at 2:14 am

#273299

Hi I have replication set up between two servers, but I notice every time the job named "Distribution clean up: distribution" (by default) runs on the distributor, I get blocking issues where sp_MSadd_distribution_history called by the subscriber is being blocked by sp_MSsubscription_cleanup running on the distributor. Can anyone shed any light on why this is happening? Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

rraja Default port Points: 1486 More actions · Answer 1

blocking gets cleared automaticaly or you have to manually kill the blocked transaction?

jdrinkwater SSC-Addicted Points: 461 More actions · Answer 2

Yeah they do resolve without intervention, I've not had to manually kill any processes but I'd still like to know if there's any way I can avoid them entirely? I use some monitoring software and this issue is logging blocking events hundreds of times a day because the clean up job runs every ten minutes by default.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

jdrinkwater (4/27/2012)
Hi I have replication set up between two servers, but I notice every time the job named "Distribution clean up: distribution" (by default) runs on the distributor

set this job run on short intervals , sometimes longer execution cause blocking.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

jdrinkwater SSC-Addicted Points: 461 More actions · Answer 4

I can't increase the frequency of the job as currently its taking on average 5-6 minutes to complete.

Any ideas why it may be so long & causing these blocking issues?

thanks

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Set trace on performance monitor see if there are high IO spikes

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

jdrinkwater SSC-Addicted Points: 461 More actions · Answer 6

Hi I've now narrowed it down to an issue that means the distributed transactions are NEVER gettting cleaned up for a particular publisher database. The one in question has two publications in it - one is transactional and replicates several tables and indexed views, the other is a snapshot which I setup to snapshot a load of stored procedures onto the subscriber. Having two publications seems to cause an issue with the stored procedure named sp_MSmaximum_cleanup_seqno always returning 0x00. If i dive into the DDL for this SP, there is a cursor which iterates through the agents for that publisher database ID, and the natural order puts the snapshot agent second. If i change the order so that it checks the transactional agent second, the SP returns a valid @max_cleanup_xact_seqno value and the clean up job will begin cleaning up transactions. Is there any reason for me not to permanantely modify this SP so it doesnt look at snapshot agents? i.e add the line of code into the where clause of the DDL example below it...

and (select publication_type from MSpublications where publication = a.publication) != 1

I've included the relevant part of the SP in question below...

--

-- cursor through each agent with it's smallest sub xact seqno

--

declare #tmpAgentSubSeqno cursor local forward_only for

select a.id, min(s2.subscription_seqno) from

MSsubscriptions s2

join MSdistribution_agents a

on (a.id = s2.agent_id)

where

s2.status in( @active, @initiated ) and

/* Note must filter out virtual anonymous agents !!!

a.subscriber_id <> @virtual_anonymous and */

-- filter out subscriptions to immediate_sync publications

not exists (select * from MSpublications p where

s2.publication_id = p.publication_id and

p.immediate_sync = 1) and

a.publisher_database_id = @publisher_database_id

group by a.id