Share via

Transactional Replication Common Problems

  • Article

Introduction

Now that you have the tools in place to monitor performance and know when problems occur, let’s take a look at three common transactional replication problems and how to fix them.

Problem 1

Distribution Agents fail with the error message The row was not found at the Subscriber when applying the replicated command or Violation of PRIMARY KEY constraint [Primary Key Name]. Cannot insert duplicate key in object [Object Name].

Cause

By default, replication delivers commands to Subscribers one row at a time (but as part of a batch wrapped by a transaction) and uses @@rowcount to verify that only one row was affected. The primary key is used to check for which row needs to be inserted, updated, or deleted; for inserts, if a row with the primary key already exists at the Subscriber, the command will fail because of a primary key constraint violation. For updates or deletes, if no matching primary key exists, @@rowcount returns 0 and an error will be raised that causes the Distribution Agent to fail.

Solution

If you don’t care which command is failing, you can simply change the Distribution Agent’s profile to ignore the errors. To change the profile, navigate to the Publication in Replication Monitor, right-click the problematic Subscriber in the All Subscriptions tab, and choose the Agent Profile menu option. A new window will open that lets you change the selected agent profile; select the check box for the Continue on data consistency errors profile, and then click OK. Figure 4 shows an example of the Agent Profile window with this profile selected. The Distribution Agent needs to be restarted for the new profile to take effect; to do so, right-click the Subscriber and choose the Stop Synchronizing menu option. When the Subscriber’s status changes from Running to Not Running, right-click the Subscriber again and select the Start Synchronizing menu option.

Figure 4: Continue on Data Consistency Errors Profile Selected in the Distribution Agent’s Profile

This profile is a system-created profile that will skip three specific errors: inserting a row with a duplicate key, constraint violations, and rows missing from the Subscriber. If any of these errors occur while using this profile, the Distribution Agent will move on to the next command rather than failing. When choosing this profile, be aware that the data on the Subscriber is likely to become out of sync with the Publisher.

If you want to know the specific command that’s failing, the sp_browsereplcmds stored procedure can be executed at the Distributor. Three parameters are required: an ID for the Publisher database, a transaction sequence number, and a command ID. To get the Publisher database ID, execute the code in Listing 1 on your Distributor (filling in the appropriate values for Publisher, Subscriber, and Publication).

Listing 1: Code to Acquire the Publisher’s Database ID

SELECT  DISTINCT
     subscriptions.publisher_database_id
FROM    sys.servers AS [publishers]
     INNER JOIN distribution.dbo.MSpublications AS [publications]
          ON publishers.server_id = publications.publisher_id
     INNER JOIN distribution.dbo.MSarticles AS [articles]
          ON publications.publication_id = articles.publication_id
     INNER JOIN distribution.dbo.MSsubscriptions AS [subscriptions]
          ON articles.article_id = subscriptions.article_id
               AND articles.publication_id = subscriptions.publication_id
                    AND articles.publisher_db = subscriptions.publisher_db
                    AND articles.publisher_id = subscriptions.publisher_id
     INNER JOIN sys.servers AS [subscribers]
          ON subscriptions.subscriber_id = subscribers.server_id
WHERE   publishers.name = 'MyPublisher'
          AND publications.publication = 'MyPublication'
          AND subscribers.name = 'MySubscriber'

To get the transaction sequence number and command ID, navigate to the failing agent in Replication Monitor, open its status window, select the Distributor to Subscriber History tab, and select the most recent session with an Error status. The transaction sequence number and command ID are contained in the error details message. Figure 5 shows an example of an error message containing these two values.

Figure 5: An Error Message Containing the Transaction Sequence Number and Command ID

Finally, execute the code in Listing 2 using the values you just retrieved to show the command that’s failing at the Subscriber. Once you know the command that’s failing, you can make changes at the Subscriber for the command to apply successfully.

Listing 2: Code to Show the Command that’s Failing at the Subscriber

EXECUTE distribution.dbo.sp_browsereplcmds
    @xact_seqno_start = '0x0000001900001926000800000000',
    @xact_seqno_end = '0x0000001900001926000800000000',
    @publisher_database_id = 29,
    @command_id = 1

Problem 2

Distribution Agent fails with the error message Could not find stored procedure 'sp_MSins_<table_name>'.

Cause

The Publication is configured to deliver INSERT, UPDATE, and DELETE commands using stored procedures, and the procedures have been dropped from the Subscriber. Replication stored procedures aren't considered to be system stored procedures and can be included using schema comparison tools. If the tools are used to move changes from a non-replicated version of a Subscriber database to a replicated version (e.g., migrating schema changes from a local development environment to a test environment), the procedures could be dropped because they don’t exist in the non-replicated version.

Solution

This is an easy problem to fix. In the published database on the Publisher, execute the sp_scriptPublicationcustomprocs stored procedure to generate the INSERT, UPDATE, and DELETE stored procedures for the Publication. This procedure only takes one parameter—the name of the Publication—and returns a single nvarchar (4000) column as the result set. When executed in SSMS, make sure to output results to text (navigate to Control-T or Query Menu, Results To, Results To Text) and that the maximum number of characters for results to text is set to at least 8,000. You can set this value by selecting Tools, Options, Query Results, Results to Text, Maximum number of characters displayed in each column). After executing the stored procedure, copy the scripts that were generated into a new query window and execute them in the subscribed database on the Subscriber.

Problem 3

Distribution Agents won’t start or don’t appear to do anything.

Cause

This typically happens when a large number of Distribution Agents are running on the same server at the same time; for example, on a Distributor that handles more than 50 Publications or Subscriptions. Distribution Agents are independent executable that run outside of the SQL Server process in a non-interactive fashion (i.e., no GUI). Windows Server uses a special area of memory called the non-interactive desktop heap to run these kinds of processes. If Windows runs out of available memory in this heap, Distribution Agents won’t be able to start.

Solution

Fixing the problem involves making a registry change to increase the size of the non-interactive desktop heap on the server experiencing the problem (usually the Distributor) and rebooting. However, it’s important to note that modifying the registry can result in serious problems if it isn't done correctly. Make sure to perform the following steps carefully and back up the registry before you modify it:

  1. Start the Registry Editor by typing regedit32.exe in a run dialog box or command prompt.
  2. Navigate to the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems key in the left pane.
  3. In the right pane, double-click the Windows value to open the Edit String dialog box.
  4. Locate the SharedSection parameter in the Value data input box. It has three values separated by commas and should look like the following:

SharedSection=1024,3072,512

The desktop heap is the third value (512 in this example). Increasing the value by 256 or 512 (i.e., making it a value of 768 or 1024) should be sufficient to resolve the issue. Click OK after modifying the value. Rebooting will ensure that the new value is used by Windows. For more information about the non-interactive desktop heap, see "Unexpected behavior occurs when you run many processes on a computer that is running SQL Server."

Monitoring Your Replication Environment

When used together, Replication Monitor, tracer tokens, and alerts are a solid way for you to monitor your replication topology and understand the source of problems when they occur. Although the techniques outlined here offer guidance about how to resolve some of the more common issues that occur with transactional replication, there simply isn't enough room to cover all the known problems in one article. For more tips about troubleshooting replication problems, visit the Microsoft SQL Server Replication Support Team’s REPLTalk blog.