Export (0) Print
Expand All

Replication Merge Agent Error Messages

SQL Server 2000

  Topic last updated -- January 2004

This topic discusses troubleshooting tips and techniques for the Replication Merge Agent. Cause and resolution information for the following common error messages returned by the Merge Agent are provided:

  • "Failed to enumerate changes in the filtered articles."

  • "The merge process could not initialize the replication provider."

  • "Another merge agent for the subscription(s) is running."

  • "Waiting on backend connection"

  • "The process could not change generation history at the 'Subscriber'."
Message Text

Failed to enumerate changes in the filtered articles.

Explanation

When processing changes to filtered articles during the download phase of the merge replication process, the Replication Merge Agent executes the internal sp_MSsetupbelongs stored procedure. When articles are filtered, this stored procedure is used to determine whether changes should be sent to a Subscriber. This error can occur because of a Replication Merge Agent time-out occurs while processing these filtered changes. The time-out might be caused by one of the following:

Action

The first step in resolving this issue is to increase the time-out setting for the Merge Agent. You can set this time-out by specifying the -QueryTimeOut argument when starting the Replication Merge Agent. For more information, see Replication Merge Agent Utility.

To specify a value for the -QueryTimeOut parameter

  1. In SQL Server Enterprise Manager, expand Replication Monitor and the Agent folder.

  2. Expand the folder for the Merge Agent.

  3. Right-click the agent, and then click Agent Properties.

  4. In the Agent Properties dialog box, click the Steps tab.

  5. Select the Run Agent step, and then click Edit.

  6. In the Command text box, add -QueryTimeOut to the end of the command. The command line should look like:
    -Publisher [SamplePub] -PublisherDB [SamplePubDB] -Publication [SamplePublication] 
    -Subscriber [SampleSub] -SubscriberDB [SampleSubDB] -Distributor [SampleDist] 
    -DistributorSecurityMode 1 –QueryTimeOut
    

    Note  Line breaks in the previous example are added to improve readability. In the Command text box, parameters must be provided on a single line.

  7. To save changes and close, click OK.

In addition, you should also work to improve the overall efficiency of the download by doing the following:

  • Ensure that columns used for filtering are properly indexed and rebuild such indexes if necessary. For more information, see Rebuilding an Index.

  • When using join filters, if the join condition is based on a unique column, the join_unique_key property should be set for the article for best performance. For more information, see Join Filters.

  • Simplify the filtering criteria by denormalizing the filtered tables to reduce the number of join filters needed.

  • Avoid making large numbers of changes on filtered tables between merges, or run the Merge Agent more frequently.

To change a Merge Agent schedule

  1. In SQL Server Enterprise Manager, expand Replication Monitor and the Agent folder.

  2. Expand the folder for the Merge Agent.

  3. Right-click the agent, and then click Agent Properties.

  4. In the Agent Properties dialog box, click the Schedules tab.

  5. Select the existing schedule, and then click Edit.

  6. In the Edit Job Schedule dialog box, click Change.

  7. In the Edit Recurring Job Schedule dialog box, specify a more frequent schedule.

  8. Click OK three times.
Message Text

The merge process could not initialize the replication provider.

Explanation

The most likely cause is that the replication provider library file is missing or corrupt. For SQL Server Subscribers, this library file is Replprov.dll, and for Microsoft Access Subscribers, this library file is Msrpjt40.dll. Because these library files is installed by SQL Server, this problem is more likely to occur when replication components are deployed independently from SQL Server, as when deploying applications using the SQL Merge ActiveX control or with an Access Subscriber.

Action

If this error occurs when the Subscriber is an instance of SQL Server, you may have to rerun Setup to repair or reinstall Replprov.dll. When deploying client applications using the SQL Merge ActiveX control, ensure that this file is properly installed and registered. When the Subscriber is an Access database, ensure that Msrpjt40.dll is installed and registered properly.

Note  You can register these components using the Microsoft Windows® Regsvr32.exe utility.

Message Text

Another merge agent for the subscription(s) is running.

Explanation

The most likely cause of this error is the existence of an orphaned process resulting from an earlier, unhandled termination of Merge Agent.

Action

SQL Server cleans up orphaned processes at regular intervals. To remedy this situation immediately, you can terminate the process by using the KILL command. For more information, see KILL. The system process ID (SPID) of the orphaned Merge Agent process can be determined by querying the sysprocesses system table for the value of the spid column when the value of the program_name column is "Replication Merge Agent". For more information, see sysprocesses.

Message Text

Waiting on backend connection

Explanation

This error occurs when Merge Agent process threads running at the local server must wait for threads running at the remote server to complete before applying changes. This can be caused by a poorly performing query or process.

Action

When attempting to trace this error back to a poorly performing query, you can locate the SPID for the Replication Merge Agent from the sysprocesses table and then execute DBCC INPUTBUFFER for this SPID. For more information, see sysprocesses and DBCC INPUTBUFFER. You should improve the indexes on any user tables involved in poorly performing queries. You can also attempt to resolve this issue by simply improving the overall efficiency of the merge process by doing the following:

  • Ensure that columns used for filtering are properly indexed and rebuild such indexes if necessary. For more information, see Rebuilding an Index.

  • Rebuild indexes on the merge replication system tables.

  • When using join filters, if the join condition is based on a unique column, the join_unique_key property should be set for the article for best performance. For more information, see Join Filters.

  • Simplify the filtering criteria by denormalizing the filtered tables to reduce the number of join filters needed.

  • Decrease the retention period for the publication, if possible.

    Note  You can use sp_changemergerepublication to change the retention period of an existing publication, or you can change this publication property on the Publication Properties, General Tab in SQL Server Enterprise Manager. For more information, see sp_changemergepublication.

Message Text

The process could not change generation history at the 'Subscriber'

Explanation

This message is most likely the result of contention on the merge replication system tables that can occur when these tables have grown excessively large.

Action

To resolve this issue, reduce the settings for the -DownloadGenerationsPerBatch and -UploadGenerationsPerBatch arguments when calling the Replication Merge Agent directly, or for scheduled synchronizations, use the slow link agent profile for the Merge Agent, because this also reduces batch sizes. For more information, see Agent Profiles.

Note  The default value for both -DownloadGenerationsPerBatch and -UploadGenerationsPerBatch is 100.

To specify a value for the -DownloadGenerationsPerBatch and -UploadGenerationsPerBatch parameters

  1. In SQL Server Enterprise Manager, expand Replication Monitor and the Agent folder.

  2. Expand the folder for the Merge Agent.

  3. Right-click the agent, and then click Agent Properties.

  4. In the Agent Properties dialog box, click the Steps tab.

  5. Select the Run Agent step, and then click Edit.

  6. In the Command text box, add -DownloadGenerationsPerBatch or -UploadGenerationsPerBatch to the end of the command, and then type a value for the number of generations per batch. With both arguments, the command line should look like:
    -Publisher [SamplePub] -PublisherDB [SamplePubDB] -Publication [SamplePublication] 
    -Subscriber [SampleSub] -SubscriberDB [SampleSubDB] -Distributor [SampleDist] 
    -DistributorSecurityMode 1 -DownloadGenerationsPerBatch 100 
    -UploadGenerationsPerBatch 20
    

    Note  Line breaks in the previous example are added to improve readability. In the Command text box, parameters must be provided on a single line.

  7. To save changes and close, click OK.

    Note  You can also try lowering the retention period for the publication. Use sp_changemergepublication to change the retention period of an existing publication, or you can change this publication property on the Publication Properties, General Tab in SQL Server Enterprise Manager. For more information, see sp_changemergepublication.

See Also
Replication Merge Agent
Show:
© 2014 Microsoft