none
Initialization takes very long in the Merge process

    Question

  • The merge replication has suddenly slowed down for all our users drastically.

    The Synch that took 3-4 mins originally takes around 20-25 mins now.

    when i see the details of each Merge, i find the time taken to "Initialization" is the maximum.

    the download of data and updates is not very long time taking.

    Please help what is causing this time gap.

    Thanks

    Friday, March 23, 2012 5:59 AM

Answers

  • Hi cheerfulwish,


    Please try to install the SQL Server latest Service Pack to see that if it is would work.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Monday, March 26, 2012 9:33 AM
    Moderator

All replies



  • For such delay circumstances , it might have some probable assumptions such as :





    1. Much Bulk DML transactions  (on publisher or
      subscriber )impacting on replication queue yielding to latency of reflecting
      other transactions
    2. A lot of triggers exists there that might break down reflection process while committing transactions at
      subscriber
    3. Some DDL changes took place there that might break down replication process due to some syntax errors
    4. Overload on Subscriber DB Server either on Server Level (CPU/Memory/Network/Storage) or DB level
      (Heavy DML transactions /Log file space/Log flush wait /Log write waits…etc)
    5. Latency in network connection between publisher + subscriber


















    • Edited by ShehapMVP Friday, March 23, 2012 2:23 PM
    Friday, March 23, 2012 2:10 PM
  • It is possible you have fragmentation in the Merge system tables.  You'll want to inspect the avg_fragmentation_in_percent in sys.dm_db_index_physical_stats for the Merge system tables - MSmerge_contents, MSmerge_genhistory, MSmerge_tombstone, MSmerge_current_partition_mappings, and MSmerge_past_partition_mappings.  Periodically re-index these tables.

    You want this to be as close to zero as possible.  0 - 5% is acceptable, 5 - 30% calls for a reorganization, and > 30% calls for a rebuild.

    This may or may not resolve your issue, ymmv.


    Brandon Williams (blog | linkedin)

    Friday, March 23, 2012 6:05 PM
    Moderator
  • Hi,

    We have not made any changes recently.

    The triggers and DML DDL all are exactly the same as always.

    It is unlikely that server get very loaded so irreasonably.

    Sunday, March 25, 2012 9:41 AM
  • Hi cheerfulwish,


    Please try to install the SQL Server latest Service Pack to see that if it is would work.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Monday, March 26, 2012 9:33 AM
    Moderator