1

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.

Sean Fitzgerald
  • 11
  • 3
  • 1
    What does sp_helpdistributor return for min distrib retention and max distrib retention? – Brandon Williams Jan 13 '13 at 05:36
  • Also, what is the publication property value immediate_sync set to? – Brandon Williams Jan 13 '13 at 07:36
  • Hi Brandon, thanks for responding. Min distrib retention = 0, max distrib retention = 48, immediate_sync = 0 – Sean Fitzgerald Jan 14 '13 at 02: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
  • I suspect the MSrepl_commands table is very large as well. Your scenario sounds similar to the one described here: http://www.mssqltips.com/sqlservertip/1823/troubleshooting-slow-sql-server-replication-issue-due-to-distributor-database-growth/ – Brandon Williams Jan 15 '13 at 23:20
  • You may want to try temporarily changing the retention to 120, manually run the distribution agent cleanup job, and see if that reduces the size of MSrepl_commands. This should speed up subsequent distribution cleanup job runs. – Brandon Williams Jan 15 '13 at 23:22
  • I am also aware of a distribution cleanup job bug that will occur in certain versions of SQL Server. I have detailed one here: https://connect.microsoft.com/SQLServer/feedback/details/725860/sp-msdistribution-cleanup-fails-to-remove-all-delivered-transactions#details – Brandon Williams Jan 16 '13 at 00:08
  • thanks, you are spot on with your articles and findings. I'm going to double check the solution tomorrow, and will mark this as the answer at that time!! – Sean Fitzgerald Jan 16 '13 at 02:53

0 Answers0