Background: I have a SQL Server 2005 transactional replication setup that pushes normal traffic fine, but occasional larger processing batches have been a problem.

Obviously the changes take longer to push, but even after that completes, the MRrepl_commands table gets full of old transactions and all subsequent activity involving replication is very slow (until the old records get eventually flushed away by the distribution cleanup job).

Right now there are 500 million rows in distribution.dbo.MSrepl_commands. So even the distribution job that tries to locate pending commands to send to the subscriber is very, very slow.

What should I do? Is this a problem with the history retention period or something?

I vaguely recall a thread where someone mentioned adding a non-clustered index to MSrepl_commands, but I'm not sure if that's a good idea or not. The missing index dmvs suggested an index on ([publisher_database_id], [article_id]) INCLUDE ([xact_seqno]), but I'm unsure if that's advisable, or if that would slow down other aspects of replication.

EDIT: Looks like the 500m rows are not recent like I thought, they go back 4 months, so for some reason my cleanup job is not working. Currently investigating the "subscription expiration" setting, it is set to "never expire", which I don't think is correct.

  • Is your publication properties set to allow_anonymous and immediate_sync ? If yes, change them to FALSE using sp_changepublication. Also check this article from Chris Skorlinski. – Kin Shah Apr 16 '15 at 23:31
  • In the GUI on the "subscription options" tab, are those labeled "Allow anonymous subscription" and "Allow immediate updating subscriptions"? If so, those are both set to False. – BradC Apr 17 '15 at 14:28
  • Can you verify using exec sp_helppublication @publication = 'your_publication_name' --> immediate_sync = 1 or 0 ? – Kin Shah Apr 17 '15 at 14:37
  • Confirmed, both are set to 0 (for all 4 publications). I am currently investigating the "subscription expiration" setting, 3 of the 4 are set to "never expire", where are documentation says to always use something like 720 hours. Hoping this doesn't force a reinitialization if I change that. Also not sure if its related to my retention problem. I have confirmed that the 500m rows still hanging around go back 4 months, my cleanup job is somehow not doing its job. – BradC Apr 17 '15 at 14:42
  • Great, did you check the link that I gave. It will show you how to modify the SP and then delete the unwanted or older transactions from the table. That should help you. – Kin Shah Apr 17 '15 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.