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.
In Replication Monitor “right-click” a Replication Agent and select “Agent Profile”.
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.
Click the details “. . . “ button to review the Agent Profile settings.
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.
Select the “STATISTICS SINCE AGENT STARTED”, then CTRL-C to copy stats to clipboard. Then PASTE into Notepad.exe.
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