April 27, 2012 at 2:14 am
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
April 27, 2012 at 6:38 am
blocking gets cleared automaticaly or you have to manually kill the blocked transaction?
April 27, 2012 at 7:30 am
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.
May 4, 2012 at 5:46 am
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;-)
May 8, 2012 at 6:15 am
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
May 8, 2012 at 6:30 am
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;-)
May 10, 2012 at 8:04 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply