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

Source: Internet
Author: User
Tags naming convention

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"

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.