Sql-server – The message “Running the Merge Agent process would exceed the maximum number of processes allowed” appears, but it doesn’t seem likely

merge-replicationreplicationsql serversql-server-2008-r2

The full text of the message is "Running the Merge Agent process at this time would exceed the maximum number of processes allowed by this publication. The process will run when resources become available." It couldn't be put on the title.

We have a merge publication on a database. While a subscriber is synchronising, the above message often appears and synchronisation stalls for several minutes and might even time out.

The publication's "maximum concurrent processes" setting is 20, and using SSMS's activity monitor I see only 4 processes that seem to be related to this publication. In any case, there is only one subscriber for the publication and so it seems unlikely it could be using 20 processes.

Am I correct in my assumption above (that one subscriber shouldn't use 20 processes) and, if I am, what could be causing this problem?

Searching Google for the message's text brings only two results.

This is a merge publication where both publisher and subscriber use SQL Server 2008 R2.

Best Answer

For merge replication issues, first stop is to read Chris Skorlinski's blog post at http://blogs.msdn.com/b/repltalk/archive/2010/06/28/merge-replication-capacity-planning.aspx

He links you to several resources at the top of his blog post. The post is from 2010, but merge replication has not had any significant update as far as I know.

Chris's bottom line is that there are too many variables: complexity of queries, power of the server, concurrent processes, et cetera, to be able to provide a recommended configuration. For example: "I (have) seen simple designs with little or no filtering supporting 1000 of users to very complex filtering/join design barely able to hand 10 subscribers."

One link that he particularly recommends is: http://robtiffany.com/mobile-merge-replication-performance-and-scalability-cheat-sheet/

Rob's focus is on merging replication to a SQL Server for later shipping to SQL Compact. But focus on the issues of merge replication and he has several suggestions, such as:

  • Ensure your schema is sufficiently de-normalized so that you never have to perform more than a 4-way JOIN across tables.
  • In order to prevent SQL Server from performing Metadata Cleanup every time a Subscriber synchronizes, set the –MetadataRetentionCleanup parameter to 0.
  • As SQL Server has to scale up ... locking contention will increase due to more (concurrent) Merge Agents. Adjust the parameters of the Default Profile so that both –SrcThreads and –DestThreads are equal to 1.

Note, I do not use merge replication. But by the nature of the issues with merge, there are more resources needed than in standard replication.

Related Question