Using “Verbose history agent profile.” while troubleshooting Replication

by Chris Skorlinski
Microsoft SQL Server Escalation Services

The “default” Agent Profile enables “-HistoryVerboseLevel 1” which provides limited detail in the Distribution database history tracking tables. Using Replication Agent Profile “Verbose history agent profile.” enables “-HistoryVerboseLevel 2” which provides additional diagnostic data while troubleshooting SQL Server Replication.  The “Verbose history agent profile.” can be set using Replication Monitor for all types of SQL Replication Agent. Use the “verbose” setting while troubleshooting connectivity or agent performance problem.  Once resolved, set the Profile back to “default” to reduce Agent tracking overhead.

The detailed Agent history and runtime statistics can also be written to a TEXT file using the “-output” parameter. See KB article 312292 How to enable replication agents for logging to output files in SQL Server.

How to enable “Verbose history agent profile.”

In Replication Monitor “right-click” a Replication Agent and select “Agent Profile”.

image

Click on “Verbose history agent profile.” to enable detailed logging by the Replication Agent.  You will need to stop and restart the Replication Agent for the setting to take effect.

image

Click the details “. . . “ button to review the Agent Profile settings.

image

As shown in the screen shot below, selecting “View Details” after setting “Verbose history agent profile.” provides detailed agent run history along with an Agent summary recorded every 10 minutes.

image

Select the “STATISTICS SINCE AGENT STARTED”, then CTRL-C to copy stats to clipboard.  Then PASTE into Notepad.exe.

image

The detailed Agent statistics can be used to troubleshoot SQL Server Replication performance.  See Troubleshooting Transactional Replication for more details on using Agent Statistics along with other performance monitoring tools for troubleshooting SQL Server Replication.

************************ STATISTICS SINCE AGENT STARTED ***********************
07-13-2010 22:26:56

Total Run Time (ms) : 300797     Total Work Time  : 109
Total Num Trans     : 2     Num Trans/Sec    : 18.35
Total Num Cmds      : 6     Num Cmds/Sec     : 55.05
Total Idle Time     : 300000

Writer Thread Stats
  Total Number of Retries   : 0
  Time Spent on Exec        : 78
  Time Spent on Commits (ms): 359     Commits/Sec         : 458.72
  Time to Apply Cmds (ms)   : 109     Cmds/Sec            : 55.05
  Time Cmd Queue Empty (ms) : 532     Empty Q Waits > 10ms: 2
  Total Time Request Blk(ms): 300532
  P2P Work Time (ms)        : 0     P2P Cmds Skipped    : 0

Reader Thread Stats
  Calls to Retrieve Cmds    : 63
  Time to Retrieve Cmds (ms): 109     Cmds/Sec            : 55.05
  Time Cmd Queue Full (ms)  : 0     Full Q Waits > 10ms : 0