How to enable replication agents for logging to output files in SQL Server

You can configure the SQL Server replication agents such as, the Snapshot Agent, Log Reader Agent, Queue Reader Agent, Distribution Agent, and Merge Agent to capture more information about error messages and procedure calls to a text file. For example, you can capture the additional information about error messages and procedure calls to troubleshoot agent failures.

If you encounter an agent failure, the default agent history may be inadequate to troubleshoot the principal cause of the failure. In this case, you may find verbose agent output useful. This article explains how to export verbose agent activity to a text file.

Note Configuring replication agents to output to a log file degrades performance for the replication agents because SQL Server prompts the agent to write verbose details about its activity to an output log. You should also write the log to a disk that has adequate free space to accommodate the data output by the agent. You must remove the additional parameters as soon as you identify the replication failures. Otherwise, you will have a replication agent that is continuously writing verbose output to the output log, which you do not need after you identify the cause of the failure.

The following options are provided for each of the replication agents to enable logging to an output file:

• The path of the agent output file. If the specified file name exists, the output is appended to the file.

  -Output output_path_and_file_name
  						

  For example:

  -Output C:\ReplOutput.txt
  						

• The ability to specify whether the output should be verbose. If the verbose level is 0, SQL Server only prints the error messages. If the verbose level is 1, SQL Server prints all the progress report messages. If the verbose level is 2 (default), SQL Server prints all the error messages and progress report messages, which is useful for debugging. For example:

  -OutputVerboseLevel [0|1|2]
  						

  -OutputVerboseLevel 2
  						

You can change the value for the OutputVerboseLevel parameter based on your requirements.

Note that the output file can grow to a very large size quickly. Hence, make sure there is enough space to accommodate such a large file. The exact size of the file depends on when the error occurs during the execution of the agent and when the agent stops. In addition, if you leave the parameters on after you fix the agent errors, all replication activities continue to append to the file and the program consumes disk space is very quickly.

How to enable output logging for a replication agent

You can use the following steps with Microsoft SQL Server 2000 Enterprise Manager or Microsoft SQL Server 7.0 Enterprise Manager.

Note If you need to set up the output logging for a Pull Subscription Agent with the SQL Server 7.0 Enterprise Manager, you cannot use the following steps. Refer to the "How to enable output logging for a Pull Subscription Agent with SQL Server 7.0 Enterprise Manager" heading of this article instead.

Note If you are using SQL Server 2005 to set up the output parameter for a agent, see the "How to: Work with Replication Agent Profiles (SQL Server Management Studio)" topic in SQL Server 2005 Books Online.

1. In SQL Enterprise Manager, click the Replication Monitor Node to open the Replication Monitor on the server configured as the Distributor.
2. Click Open on the Publishers node.
3. Click the publisher that has the publication that has the problem.
4. Click Publication.
5. In the right-hand pane of SQL Enterprise Manager is a list of the agents related to the publication. You see the Snapshot Agent, Log Reader Agent and the Push/Pull Subscription Agent.
6. Identify the agent for which you need to set up output logging.
7. Right-click the replication agent you identified in step 6, and then click Agent Properties.
8. Click the Steps tab, and then edit the Run Agent step.
9. At the end of the string under command, add:
   
   -Output C:\Temp\OUTPUTFILE.txt -Outputverboselevel [0|1|2]
   
   Specify either 0, 1, or 2 after the -Outputverboselevel parameter.
10. Click OK to save the changes, and then close the Edit Job Step dialog box.
11. Click OK to save the changes, and then close the Replication Agent Properties dialog box. If the agent is set to run continuously, stop and restart the replication agent so that SQL Server logs the messages to the log file specified in step 9. If the file already exists, the agent appends the output to the file.

How to enable output logging for a pull subscription agent with SQL Server 7.0 Enterprise Manager

1. In SQL Enterprise Manager, click the Subscriber database.
2. Open the Pull Subscriptions folder.
3. In the right-hand pane of SQL Enterprise Manager you will see the pull subscription.
4. Open the subscription properties by double-clicking the subscription.
5. Click the General tab to open the agent properties dialog box. Click Distribution Agent Properties for a transactional pull subscription and click Merge Agent Properties for a merge pull subscription.
6. Click the Steps tab, and then edit the Run Agent step.
7. At the end of the string under command, add:
   
   -Output C:\Temp\OUTPUTFILE.txt -Outputverboselevel [0|1|2]
   
   Specify either 0, 1, or 2 after the -Outputverboselevel parameter.
8. Click OK to save the changes, and then close the Edit Job Step dialog box.
9. Click OK to save changes, and then close the Replication Agent Properties dialog box. If the agent is set to run continuously, stop and then restart the replication agent so that SQL Server logs the messages to the log file you specified in step 7. If file already exists, the agent appends the output to a file.

SQL Server 2000 Books Online; topics: "Replication Agents"; "Replication Snapshot Agent Utility"; "Replication Log Reader Agent Utility"; "Replication Distribution Agent Utility"; "Replication Queue Reader Agent Utility"; "Replication Merge Agent Utility"