Handling Agent Errors

SQL Replication


Handling Agent Errors

You can monitor details about the current activity and the task history of each replication agent in Replication Monitor. As an agent operates, it writes details of its activity and messages to the history table in the Distributor.

You can display errors if the agent has encountered any during an agent session. The error details are displayed in the Error Detail dialog box. You can also display error information in the Session Details dialog box, or right-click an agent and then click Error Details.

The replication agent error status is represented in SQL Server Enterprise Manager as an icon at each agent and each node under Replication Monitor. To have the icon correctly reflecting the status of the replication agents, you must refresh the node manually or enable automatic refreshing.

You can use the Refresh Rate and Settings dialog box to:

  • Enable or disable automatic refreshing for the console tree or details pane.

  • Specify the refresh period in seconds for the console tree or details pane.

  • Specify the inactivity threshold for restarting replication agents.

  • Specify the Windows NT Performance Monitor or Windows 2000 System Monitor file setting for replication performance.

You can also customize the columns displayed in the details pane when the selection is on a publication or an agent view. Use the Select Columns dialog box to select which columns to display.

You can use the Select Columns dialog box to select columns to display when the focus is on any of these nodes:

  • A transactional, snapshot, or merge publication

  • Snapshot Agent

  • Log Reader Agent

  • Distribution Agent

  • Merge Agent
Skipping Errors in Transactional Replication

During transactional replication, you can specify errors that can be skipped during the distribution process. Typically, when the Distribution Agent is running in continuous mode and it encounters an error, the agent, and the distribution process, stops. By specifying expected errors or errors that you do not want to interfere with replication, the agent will log the error information and then continue running.

The most typical way to skip errors is using the Distribution Agent profile titled Continue On Data Consistency Errors. To use this profile, right-click on the Distribution Agent, click Profiles, and then select this profile. The Distribution Agent will then skip errors 2601, 2627, and 20598. You can also create your own agent profile and specify the –SkipErrors parameter with the errors you want skipped. For more information on creating profiles, see Agent Profiles.

Caution  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). Skipping errors should only be used with caution and with the understanding of what the error is, why it is occurring, and why it needs to be skipped rather than solved.

You can specify the errors that should be skipped using the SQL Distribution ActiveX® Control, in an agent profile (with the –SkipErrors parameter on the Distribution Agent profile), or by using the –SkipErrors parameter in the command line for the Distribution Agent.

For example, if the Distribution Agent returns a duplicate key violation error, but you would want the distribution process to continue and log only the error information, you can specify the –SkipErrors command line parameter with the number of the error that should be skipped.

Typically, the Distribution Agent is a shared agent servicing multiple publications and multiple articles. If you specify –SkipErrors on the agent, all publications that use that Distribution Agent will be affected. If you want to skip an error on one specified publication, set up the publication with an independent agent and then specify the –SkipErrors command line parameter for that agent.

To specify the –SkipErrors parameter on the agent command line

  1. At the Distributor, expand Replication Monitor, click the Distribution Agents folder, right-click an agent, and then click Agent Properties.

  2. On the Steps tab, double click the Run agent step.

  3. In the command text box, type –SkipErrors and specify the error numbers that you want skipped if this agent encounters them (errors are delimited; list them with colons between each error number).

To change replication monitor refresh rate and settings