To troubleshoot replication agent failures, replication agent profiles can be configured to output verbose agent logs to a text file. All replication agents contain the parameters -Output output_path_and_file_name and -OutputVerboseLevel [0|1|2], which when specified, output the agent log to the file output_path_and_file_name and will contain varying degrees of information depending on the verbose level passed. In general, the information contained in the verbose agent logs consists of progress report messages, error messages, procedure calls, and various session statistics.
Depending on the verbose level and the agent, some or all of this information will be provided. For the purpose of troubleshooting replication agent failures, a verbose level of 2 should be specified, which prints all error and progress report messages. It might also be worth mentioning that some agents can be passed undocumented output verbose levels 3 and 4 which provide even more information. The information provided in the verbose agent logs can be useful when trying to diagnose and identify exactly where replication is failing as well as get detailed information as to the root cause of the failure. In addition, the verbose agent logs are likely the first piece of diagnostic data that Microsoft Support will ask for when diagnosing a replication support incident.
Reading the logs takes a little getting used to but after utilizing them to troubleshoot a few times, it becomes second nature. I often use them when replication failures are occurring and the error given in Replication Monitor and the View Synchronization Status window isn’t sufficient enough to diagnose the issue. An example error message with Merge Replication that occasionally crops up is: The process was successfully stopped. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200963), which isn’t very verbose. Knowing how to output the verbose agent logs is a smart skill to have under these circumstances in order to identify the root cause.
The parameters to output the verbose agent logs can be passed to the Run Agent job step in the Job Properties dialog, Agent Profile dialog in Replication Monitor, via RMO, or by way of invoking the agents from the command line. After adding the parameters, the replication agent must be restarted for the profile changes to take effect. Once restarted, the replication agent will begin outputting the verbose log to the path and file name specified. After reproducing the failure, the log can then be examined for further information.
The verbose agent log is outputted in the form of a text document and can be opened with your favorite text editor. The -OutputVerboseLevel specified for the agent profile will dictate the contents of the log. Typically, if a replication agent failure occurs, it will fail and the process will stop. That means that the failure is likely to be found at the end of the log, in the last 20 or so lines in the output file.
Be sure to remove the agent parameters after reproducing the failure and collecting the log. Not doing so will result in the log to be appended to on subsequent synchronizations and consume disk space. Outputting agent logs can also impact replication performance and thus isn’t recommended to do unless troubleshooting replication failures.