Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am using SQL Server transactional replication, and have noticed that on a daily basis all of the publications for a distributor are being blocked when the distribution agent cleanup job executes. I have seen this block persist for up to 2 hours. Some of the subscriptions require extremely low latency, so this is a big problem for me right now.

I have read that setting the HistoryVerboseLevel to 0 will stop logging to MSdistribution_history database. Having done this, the blocking subsided for the subscriptions that i changed the distributor profiles for. However, within three days all of the subscriptions were marked inactive.

I'm out of ideas at this point, so was hoping that someone else has a solution. My next step is try enabling snapshot isolation on the distribution database, but feel that this is a "sledge hammer" approach.

share|improve this question
1  
What does sp_helpdistributor return for min distrib retention and max distrib retention? –  Brandon Williams Jan 13 '13 at 5:36
    
Also, what is the publication property value immediate_sync set to? –  Brandon Williams Jan 13 '13 at 7:36
    
Hi Brandon, thanks for responding. Min distrib retention = 0, max distrib retention = 48, immediate_sync = 0 –  Sean Fitzgerald Jan 14 '13 at 2:42
    
What does sp_helppublication return for retention? Does it return 0? USE PubDB EXEC sp_helppublication @publication = 'PublicationName' –  Brandon Williams Jan 14 '13 at 20:03
    
Sorry for the delay in reply, wife and 1 kiddo have the flu ... tough day. Retention = 0 for the pub. –  Sean Fitzgerald Jan 15 '13 at 23:05
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.