KB article 312292 How to enable replication agents for logging to output files in SQL Server
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
Start Replication Monitor, select Publication then All Subscriptions, or Agent. Below I’ve selected Agent, then LogReader Agent, then Properties.
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.
******************** STATISTICS SINCE AGENT STARTED *************************** 01-05-2010 21:56:20 Execution time (ms): 624 Work time (ms): 437 Distribute Repl Cmds Time(ms): 437 Fetch time(ms): 421 Repldone time(ms): 16 Write time(ms): 0 Num Trans: 1 Num Trans/Sec: 2.288330 Num Cmds: 1 Num Cmds/Sec: 2.288330 *******************************************************************************
Tran or Merge Subscriber
Setting the Merge Subscriber or Transactional Subscriber requires couple of extra steps. You need to select View Details
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.
