Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

Your Answer


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

Browse other questions tagged or ask your own question.