Using “Verbose history agent profile.” while troubleshooting Replication

Share this Post

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.

logread.exe <job parameters>

Statistics appear automatically when executing, then stopping LogRead.exe via CMD-prompt providing immediate feedback on throughput while adjusting Log Reader parameters.  Output includes a breakout by article showing table\article and type of command was being processed by the Log Reader.

With SQL Agent Log Reader job stopped, paste the job parameters along with LogRead.exe into admin cmd-prompt>: C:\Program Files\Microsoft SQL Server\130\COM\logread.exe <SQL Agent job parameters>

Allow Log Reader to run for period of time, then stop with CTRL-C, statistics appear in the cmd-console.

Distrib.exe <job parameters>


Share this Post

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.