Subscriber Error: The row was not found at the Subscriber when applying the replicated command


The error “The row was not found at the Subscriber when applying the replicated command” can occur for three reasons:  1) A publication is setup using a no-sync initialization.  2)  Data was changed on the subscriber (which should never occur in a read-only environment).  3)  Either an update or delete statement is occurring on a record

that doesn’t exist.   This last error can occur when there is a relationship that exists on a set of tables (such as a cascade) that depend on one another and one table has a completed transaction occur before the dependent table is updated.

For example, transaction A issues an insert statement for record 1 and transaction B issues an update statement for record 1; however, transaction B gets sent through before transaction A.

When this error occurs, it may be possible to wait a few minutes and attempt a re-sync to correct the error.  If doing so does not correct the error, then the transaction that is causing the error needs to be looked up and verified to determine if it exists on the subscriber.

Running the stored procedure Usp_GetProblematicTransaction will assist in identifying the problematic transaction.

If the row does not exist in the subscriber then by following one of the steps below will assist in fixing the error without re-initializing the publication:

EXAMPLE (replication command being issued):

  CALL [sp_MSupd_dbotblDelays] (,,,,,,,,,1,25451,,,,,,,{ts '2007-04-30

                                13:04:00.360'},25451,8424052,0x000606)}

Repeat steps 1 through 5 until there are no more errors or you have manually inserted 5 records into the subscriber.  As a rule of thumb, more than 5 record errors may be an indicator of data integrity errors.  If this is the case, re-initializing the table may be the best path to take.

  1. Execute the stored procedure Usp_GetProblematicTransaction from the publisher server.
  2. Using the example above, log on to the subscriber server and select from the tblDelays tables where the key field is equal to 8424052.  (Note:  the primary key is always located in the second to last column of the transaction)
  3. If the record does not exist change, the connection to the publisher database and run the same query to retrieve the record needed.
  4. Write an insert statement using the values from step 3 and change the connection back to the subscriber and execute the query.
  5. Check to see if the agent in error has started automatically.  If it has not, then stop and restart the agent.  Refresh the agent a few times to make sure you do not see any more error messages such as ‘error executing a batch of commands’.  This is an indication that there is another row not found error.

NOTE:  If the error continues after 5 tries than the publication will have to be re-initialized in the evening.  In order to prevent any delays in the publication change the distribution agent profile to skip the error

  1. Within Replication Monitor right click on the subscriber agent à Agent Profile à Continue on data consistency errors
  2. Stop and start the agent for the new profile to be used
  3. After the publication has been re-initialized change the distribution agent back to its original profile.

Another method is to execute the Tablediff.exe utility for the table in error.  To utilize this method, follow these steps:

  1. Execute the tablediff.exe with a file output specified.
  2. Open the created SQL file on the subscriber
  3. Temporarily change the distribution agent profile:
    1. Within Replication Monitor right click on the subscriber agent à Agent Profile à Continue on data consistency errors
    2. Stop and start the agent for the new profile to be used
  4. Utilize the new profile for a day in order to avoid any more errors.  Otherwise, duplicate rows that may occur from running the script in step 2.

If you run the stored procedure Usp_GetProblematicTransaction and the transaction returned is a delete transaction, than follow the following steps:

  1. Run a select statement on the subscriber passing in the primary key to verify if the record exists.
  2. If the record does not exist, than delete the command by executing the stored procedure Usp_DeleteProblematicTransaction.
  3. Check to see if the agent in error has started automatically.  If it has not, then stop and restart the agent.  Refresh the agent a few times to make sure you do not see any more error messages such as ‘error executing a batch of commands’.  This is an indication there is another row not found.
Advertisements
This entry was posted in Replication. Bookmark the permalink.

One Response to Subscriber Error: The row was not found at the Subscriber when applying the replicated command

  1. MOIZ says:

    Thanks Ishtiaq!

    Just to add another point, this error can occur if the Article is being published more than once i.e. thru more than one publication to the same subscriber database. In such a case, if a delete stmt is being propagated then, it gets pushed by the one of the publications and when the other publication tries to apply the delete record script, it throws the error since the record already got delete thru one of the other publication.

    Regards

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s