Failed to copy due to data conflicts in SQL Server replication

SQL Server replication is a commonly used feature to improve performance and availability. However, there are many unstable factors in this feature, which may cause the failure of the replication function, today, I encountered a problem because of the transaction replication failure caused by SQL Server data conflicts. To solve this problem, reinitialize replication to ensure data consistency on both sides. You can also skip failed transactions, so that although some data may be inconsistent, if you can ensure that this part of data is not critical data, you can use this solution.

To skip a failed transaction, two SP s are required. The first is sp_helpsubscriptionerrors, which is used to query which transaction causes data conflict. This stored procedure is executed in the distributor's distribution database.

Syntax

Copy

Sp_helpsubscriptionerrors [@ publisher =] 'her her ', [@ publisher_db =] 'her her _ db', [@ publication =] 'publication', [@ subscriber =] 'subscriber ', [@ subscriber_db =] 'subscriber _ db'

In the returned tableXact_seqnoThe column is the transaction ID we are looking.

In this case, we can use our second SP: sp_setsubscriptionxactseqno.

Syntax

Copy

Sp_setsubscriptionxactseqno [@ publisher =] 'her her ', [@ publisher_db =] 'her her _ db', [@ publication =] 'publication', [@ xact_seqno =] xact_seqno

This stored procedure is executed on the subscription server (Business Database. The non-SQL Server subscription server does not support this process.

After the transaction is successfully executed, you can skip the transaction and copy the transaction to continue the execution. Of course, it is best to verify whether there is a missing or inconsistent key data after the replication fix.