Use the "-SkipErrors" parameter in Distribution Agent cautiously

Article translations Article translations
Article ID: 327817 - View products that this article applies to.
This article was previously published under Q327817
Expand all | Collapse all

SUMMARY

Microsoft SQL Server 2000 and later includes the new parameter -SkipErrors in the Distribution Agent that permits the Distribution Agent to skip the indicated error in transactional replication and to continue the distribution process.

The following excerpt is from the "Handling Agent Errors" topic in SQL Server Books Online:
Under typical replication processing, you should not experience any errors that need to be skipped. The ability to skip errors during transactional replication is available for the unique circumstances where you expect errors and do not want them to affect replication (for example, when failing over to a secondary Publisher during log shipping).
Microsoft recommends that you use this parameter cautiously and only when you have a good understanding of the following:
  • What the error indicates.
  • Why the error occurs.
  • Why it is better to skip the error instead of solving it.
If you do not know the answers to these items, inappropriate use of the -SkipErrors parameter may cause data inconsistency between the Publisher and Subscriber. This article describes some problems that can occur when you incorrectly use the -SkipErrors parameter.

MORE INFORMATION

In transactional replication, data changes at the Publisher are propagated to the Subscriber in the unit of transaction.

In one transaction, there may be multiple commands. By default, if one command fails, the whole replication transaction rolls back at the Subscriber. If you add the -SkipErrors parameter to permit the Distribution Agent to skip certain errors, the individual command that causes this error is not applied to the Subscriber, but all other commands in the same transactions are applied. In this situation, the replication transaction is only partially applied to the Subscriber, which can therefore cause the data inconsistency between the Publisher and the Subscriber.

For example:

You have a transaction waiting to be replicated to the Subscriber table named T1. This transaction includes 100 insert statements. When it applies to the Subscriber, the first 90 inserts process correctly; however, the ninety-first insert statement fails and the primary key violation error 2627 occurs.

When you do not use the "-SkipErrors" parameter (default behavior):

By default, the whole transaction rolls back, and none of the 100 new records are inserted into the subscribing table. In this situation, you must fix the replication error so that the transaction can be re-applied to the Subscriber.

When you use the "-SkipErrors" parameter:

The Distribution Agent logs the error in the Distribution Agent history, it skips this error, and then it continues the distribution process. Therefore, except for the ninety-first new record that caused the error, the other 99 new records are inserted into the subscribing table. This transaction is not re-applied by the Distribution Agent, even after you manually fix the error at the Subscriber. Therefore, in this situation, the Subscriber is missing the ninety-first new record and a data inconsistency problem occurs.

You also must be aware that, in SQL Server 2000 typically the Distribution Agent is shared by multiple publications (by default, there is one Distribution Agent per publication database and subscription database pair), so if you add the -SkipErrors parameter to the Distribution Agent job, it affects all the publications that this agent is servicing. In SQL 2005 and SQL 2008 versions, transactional replication uses independent agents by default for publications created in the New Publication Wizard. For publications created using sp_addpublication stored procedure, the default behavior is to use a shared agent.

To use the -SkipErrors parameter for a specific publication, use an independent agent, which just services one subscription. To use an independent agent, follow the steps for your version:

SQL 2000

  1. In SQL Server Enterprise Manager, right-click the publication, click Properties, and then on the Subscription Options tab, click the use a Distribution Agent that is independent of other publication from this database option.
    NOTE: You cannot turn this option on after you add any subscriptions to this publication.
  2. Add the -SkipErrors parameter to the Distribution Agent of a specific subscription.

SQL 2005 and SQL 2008

  1. In SQL Server management studio, navigate to Replication and then in the Local Publications section right-click the publication, click Properties, and then on the Subscription Options page, change the value of Independent Distribution Agent from False to True.
    NOTE: You cannot turn this option on after you add any subscriptions to this publication.
  2. Add the -SkipErrors parameter to the Distribution Agent of a specific subscription.
For more information refer to the following:
Administering Replication Agents:http://technet.microsoft.com/en-us/library/ms152762.aspx
Skipping Errors in Transactional Replication:http://technet.microsoft.com/en-us/library/ms151331.aspx
sp_addpublication (Transact-SQL)http://technet.microsoft.com/en-us/library/ms188738.aspx
956376 FIX: The SQL Server 2005 Distribution Agent and the SQL Server 2008 Distribution Agent do not skip error 20598 when you configure a SQL Server 2000 transactional publication to skip error 20598

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
896609 The -skipperrors Distribution Agent parameter for SQL Server 2000 may skip more commands than expected

Properties

Article ID: 327817 - Last Review: May 28, 2009 - Revision: 6.0
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2008 Standard
Keywords: 
kbinfo KB327817

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com