SQL Server replication Delay "The line cannot be found at the subscriber when the copied command is applied"

Recently encountered "the line was not found at the Subscriber when applying the replicated command" problem, the error is as follows:

The official advice is to resynchronize and initialize the subscription, which, of course, is an option, but for hundreds of grams of production libraries, this approach consumes a lot of resources and time. You can fix the error data by locating the error data, selecting Skip, and so on after the main library is recovered from the library, to achieve the final master-slave data consistency.

Solution Ideas 1. find the transaction sequence number of the distribution task

• If replication distribution is stopped, the transaction sequence number is displayed in the error message. Is ' 0x000311cc00013a7300010000000 '.
• If the replication error message is overwritten in the display window, such as a skip error is selected, or if another error overrides the error, it can be performed at the Distributor:

Sp_helpsubscriptionerrors[@publisher =] 'Publisher', [@publisher_db =] 'publisher_db' , [@publication =] 'Publication' , [@subscriber =] 'Subscriber' , [@subscriber_db =] 'subscriber_db'

parameter corresponding values can be obtained by the following query:

• Get Publisher and Subscriber

Select *  from MSsubscriber_info

Get publisher_db and publication

Select *  from Mspublications

Execute at Subscriber, get transaction sequence number

 sp_setsubscriptionxactseqno [  @publisher =   publisher   '   [  @publisher_db =   " publisher_db  Span style= "color: #ff0000;"  > '   [  @publication =   " publication  Span style= "color: #ff0000;"  > '   [  @xact_seqno =   xact_seqno 

Example:

2. View error Commands

After getting the transaction sequence number, view the specific command for the transaction number:

Sp_browsereplcmds[[@xact_seqno_start =] 'Xact_seqno_start' ]    [, [@xact_seqno_end =] 'xact_seqno_end' ]     [, [@originator_id =] 'originator_id' ]    [, [@publisher_database_id =] 'publisher_database_id' ]    [, [@article_id =] 'article_id' ]    [, [@command_id =]command_id][, [@agent_id =]agent_id][, [@compatibility_level =]Compatibility_level]

The first two parameters are the start of the transaction number and the end of the transaction number, enter these two parameters can be

command sp_ms+operation+ _ schemaname+tablename

It is known through the example that the operation represents an update operation on the T_carpakingrecord table, and the last represents the primary key.

3. Remedial measures

Insert the full data into the from library (this is the remedy for missing row errors, and delete them directly if the insert is repeated) based on the command results analyzed above

If data consistency is not considered important, it can be skipped directly, not recommended. To skip the wrong command:

Sp_setsubscriptionxactseqno[@publisher =] 'Publisher Host name', [@publisher_db =] 'Publish Database', [@publication =] 'Publication name', [@xact_seqno =]Skipped transaction number

Select * from mspublications

SQL Server replication Delay "The line cannot be found at the subscriber when the copied command is applied"