We are using the 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. Time to time they do a mistake and modify data on subscriber and the result is that replication is broken. In this post I will show you few simple simple steps 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 distribution database to be replayed on subscriber. We will select these data from 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 xact_seqno column: it’s the starting transaction log sequence number of the first failed transaction in 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 he 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 primary key value of the row that should be deleted by this command. And because this row was deleted on subscriber manually, replication error is raised when this command is executed on 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.