Skipping Errors in Transactional Replication
For transactional replication, there are two ways to skip errors if they are encountered during the distribution process:
- The -SkipErrors parameter of the Distribution Agent, which allows you to skip errors of a certain type. The transaction with the error is not committed, but subsequent transactions are.
- The sp_setsubscriptionxactseqno stored procedure, which allows you to skip one or more transactions that cause errors. This option is not available for non-SQL Server Subscribers.
Important
Under typical replication processing, you should not experience any errors that need to be skipped. Errors should be skipped with caution and with the understanding of what the error condition is, why it is occurring, and why the error or specific transaction needs to be skipped rather than resolved. If transactions committed at the Publisher are skipped at the Subscriber, the two nodes will not be completely synchronized, which can lead to additional errors.
The -SkipErrors Parameter
By default, when the Distribution Agent encounters an error, the agent stops. If you use the -SkipErrors parameter, and specify expected errors or errors that you do not want to interfere with replication, the agent will log the error information and then continue running. For example, if you want to specify that the Distribution Agent should log duplicate key violations but continue to process subsequent transactions, specify that the agent should skip errors 2601 (Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'.) and 2627 (Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.):
-SkipErrors 2601;2627
The most common way to use the -SkipErrors parameter is to use the Distribution Agent profile titled Continue On Data Consistency Errors. The Distribution Agent will then skip errors 2601, 2627, and 20598 (The row was not found at the Subscriber when applying the replicated command). For more information, see Replication Agent Profiles. In addition to this predefined profile, you can specify the parameter in an agent profile you create or modify, or on the command line. For more information, see:
- How to: Work with Replication Agent Profiles (SQL Server Management Studio)
- How to: View and Modify Replication Agent Command Prompt Parameters (SQL Server Management Studio)
- How to: Work with Replication Agent Profiles (Replication Transact-SQL Programming)
- Programming Replication Agent Executables
Important
By default, triggers execute with the XACT_ABORT setting ON. If a statement within a trigger causes an error while the Distribution Agent is applying changes at the Subscriber, the entire batch of changes will fail, rather than the individual statement. In transactional replication, you can use the -SkipErrors parameter of the Distribution Agent to skip statements that cause errors. If -SkipErrors is used with XACT_ABORT ON, the entire batch of changes is skipped if a statement causes an error. Unless you require XACT_ABORT to be set to ON in triggers, we recommend that you set it to OFF if you are using the -SkipErrors parameter. To set the option off, specify SET XACT_ABORT OFF in the trigger definition. For more information about XACT_ABORT, see SET XACT_ABORT (Transact-SQL).
The sp_setsubscriptionxactseqno Stored Procedure
The sp_setsubscriptionxactseqno stored procedure can be used to skip one or more transactions that cause errors when applied at the Subscriber. If there is a failure and you want to skip one or more transactions:
- Execute sp_helpsubscriptionerrors at the Distributor after the Distribution Agent stops. This stored procedure returns the column xact_seqno, which contains the log sequence number (LSN) for each failed transaction.
- Execute sp_setsubscriptionxactseqno, specifying a value for the parameter @xact_seqno. If you want to skip all failed transactions, specify the largest LSN returned by sp_helpsubscriptionerrors.
For more information, see sp_setsubscriptionxactseqno (Transact-SQL) and sp_helpsubscriptionerrors (Transact-SQL).
See Also
Concepts
Replication Agents (Troubleshooting)
Replication Stored Procedures (Troubleshooting)
Troubleshooting Replication
Other Resources
Replication Distribution Agent