Tuesday 12 March 2013

SQL Server : Merge replication fails due to timeout errors


Solution for : Merge replication fails due to timeout errors


QUESTION:


You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN).

Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors.
You need to reduce the occurrence of these timeout errors. What should you do?

Answer:


When you synchronize data rows with a large amount of data, such as rows with LOB columns, Web synchronization can require additional memory allocation and hurt performance. This occurs when the Merge Agent generates an XML message that contains too many data rows with large amounts of data. If the Merge Agent is consuming too many resources during Web synchronization, reduce the number of rows sent in a single message in one of the following ways:

  • Use the slow link agent profile for the Merge Agent.
  • Decrease the -DownloadGenerationsPerBatch and -UploadGenerationsPerBatch parameters for the Merge Agent to a value of 10 or less. The default value of these parameters is 50.
Note: Merge Agent has a "slow link" profile designed for low bandwidth connections.





How to allow merge agent to use "slow link profile" :
 
Change Existing Agents:
 
Select a profile (On the General page of the Distributor Properties - <Distributor> dialog box, click Profile Defaults), and then click Change Existing Agents to specify that all existing jobs for an agent of a given type should use the selected profile. For example, if you have created a number of subscriptions to a merge publication, and you want to change the profile to specify that the Merge Agent job for each of these subscriptions should use the Slow link agent profile, select that profile, and then click Change Existing Agents.

Hope this will help you .


Brgds,



Chhavinath Mishra


Microsoft Certified IT Professional (MCITP)
Posted by at
Labels:

4 comments:

  1. Unknown11 April 2016 at 03:56

    Thank you. This helps.

    ReplyDelete
  2. Unknown11 April 2016 at 03:57

    Thank you. This helps.

    ReplyDelete
  3. Chhavinath Mishra11 January 2017 at 19:32

    Good to know that it had been helpful.

    ReplyDelete
  4. coder3 July 2018 at 16:18

    Nice article.

    But I found -DownloadGenerationsPerBatch and -UploadGenerationsPerBatch value to be 1 for slow link profile.


    I think u mean to change below values to 50 for slow link profile.
    -UploadWriteChangesPerBatch 100
    -UploadReadChangesPerBatch 100

    ReplyDelete

Subscribe to: Post Comments (Atom)