1444 - Configuring MS SQL Server Agents to Provide More Detail

You can configure the SQL Server replication agents (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 instance, 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:
  
  OutputVerboseLevel [0|1|2]
  
  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 2
  
  You can change the value for the OutputVerboseLevel parameter based on your requirements.

These options can be set in a replication agent profile using SQL Server Management Studio or Transact-SQL.

Note that the output file can quickly grow to a very large size. You must 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 very quickly.