“Repl Talk“

by Chris Skorlinski
Microsoft 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

Start Replication Monitor, select Publication then All Subscriptions, or Agent.  Below I’ve selected Agent, then LogReader Agent, then Properties.

image

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

image

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

image

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

image

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.

image

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.

image

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.