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

We are using transactional replication to publish production data for the developers that they can test bugs and other stuff on the subscriber database with near-live data. From time to time they do a mistake and modify data on subscribers and the result is that replication is broken. In this post, I will show you few simple steps on how to find more details in case your replication is reporting an error that the row was not found at the Subscriber when applying the replicated command.

Usually, you will capture this error in Replication Monitor as visible in this picture:

As a first step, we need to browse replication commands that are waiting in the distribution database to be replayed on subscribers. We will select these data from the Msrepl_errors table:

USE [distribution]
GO

SELECT 
	[id], [time], [error_code], [error_text], [xact_seqno]
FROM [dbo].[MSrepl_errors] 
ORDER BY [time] desc
GO

Once we have selected our error rising command the most important information is hidden in the xact_seqno column: it’s the starting transaction log sequence number of the first failed transaction in the execution batch.

We will use this sequence number as an input parameter for sys.sp_browsereplcmds stored procedure which returns a readable version of the replicated commands stored in the distribution database.

USE [distribution]
GO

EXEC [sys].[sp_browsereplcmds]
	 @xact_seqno_start = N'0x0000A31F0007BC9E01A200000000',
     @xact_seqno_end = N'0x0000A31F0007BC9E01A200000000'
GO

In the command column is the user-friendly text representation of the command to be replicated. The most important part of it is the number in parentheses which is in our particular case the primary key value of the row that should be deleted by this command. And because this row was deleted on the subscriber manually, a replication error is raised when this command is executed on the subscriber.

If we have the option to insert deleted rows on subscriber manually, replication will continue without an error and there is no need for subscription reinitialization.