Post reply

major slowdown after adding article to existing publication

  • March 24, 2010 at 6:38 am

    #94649

    I added a couple of relatively small tables to an existing merge replication publication. The snapshot ran successfully and completed. Since then, the merge agent has run twice, but both times had to be stopped because it ran for many hours without completing before the business day.

    I thought that when I added an article, only that article would have to be updated at the subscriber and other articles would just process normally. Am I wrong about that?

    When I take a look at sysmergearticles, I see many tables with status=6... these are not the tables I added to the publication. I read that status=6 means "new to be added". What does this mean exactly?

  • March 29, 2010 at 6:07 am

    #1141359

    Just FYI, incase anyone has a similar problem:

    This problem was created because triggers were in place to audit changes to any of the tables, and those triggers write to an audit table that is also replicated. So, when the new article was added to the publication, vast amounts of records (about 4Xs the number of records in the article) were added to the audit table.

    In addition to the larger number of records that expected, replication was slower because the retention period was not properly configure. Replication was configured with infinite retention- meaning that msmerge_contents contained almost 40 million records dating back nearly 7 years. DBCC Showcontig revealing heavy fragmentation on the index of msmserge_contents.

    After defragmenting the index on msmerge_contents to completion (14 hours), the replication ran 20 times faster.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply