Walk through of KB article 312292 How to enable replication agents for logging to output files in SQL Server

“Repl Talk“

by Chris SkorlinskiMicrosoft SQL Server Escalation Services

While troubleshooting SQL Server Replication we often want to capture detail step-by-step execution of Replication Agent. One technique is to configure the Agent to log its execution to a Text file. This posting walks through a couple of different ways to accomplish this. It is also covered in the KB article http://support.microsoft.com/?id=312292. I’ll use both Replication Monitor and JOB properties in this example.

Log Reader Agent logging

In the Agent Properties window select STEPS, Run Agent, then click EDIT

At the end of the Command window add the –output parameter. You can use almost anything for name, but I always write the log to a subdirectory.

-Output c:\temp\LogReader.out

Now start the Agent via Replication Monitor or SQL Server Management Studio. You should get a text file. You’ll find a lot of great information including performance statistics in the output file. Try it with a test publication.

Tran or Merge Subscriber

Then in the DETAILS pop-up select Action, the Job Properties. You’ll get to a similar screen where you can add the –OUPUT parameter.

Like the Log Reader, both the Distribution Agent and the Merge Agent provide detailed performance data about the Agent run.

Article Download Statistics:============================

Customer: Updates: 1 Relative Cost: 100.00%

Job Properties

I’ll show you how to get to the same setting via SSMS Job Properties. It is a little more difficult to match up the Agent with the Job, but eventually you’ll get to the same place.

The next time a Replication Agent is not working as expected, try adding –OUTPUT and look through the text file generated. It may provide clues to what the Agent is running in order to help you solve your Replication Agent problem.