Log Reader Replication Agent Test

The Replication Log Reader Agent is an executable that monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database. Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor and connects to the Publisher.

If the replication process is found to take too long to complete, then, you can monitor the operations of the Log Reader agent to figure out whether/not this agent is contributing to the slowdown. This can be achieved with the help of the Log Reader Application Agent test. This test periodically monitors the activities of the Log Reader agent, and reveals how quickly the agent copies transactions to the Distributor; in the process, the test turns your attention to latencies (if any) in the agent’s operations.

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every Microsoft SQL server monitored

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The IP address of the Microsoft SQL server.
  3. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433.
  4. instance – The name of a specific Microsoft SQL instance to be monitored. The default value of this parameter is “default”. To monitor a Microsoft SQL instance named “CFS”, enter this as the value of the “instance” parameter.
  5. ISPASSIVE – If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Commands Delivered:

Indicates the rate at which the Log Reader agent delivered commands to the Distributor.

Commands/Sec

 

Transactions Delivered:

Indicates the rate at which the Log Reader agent delivered transactions to the Distributor.

Trans/Sec

A high value is desired for this measure. If the value of this measure dips consistently over time, it is indicative of a slowdown.

Latency:

Indicates the current amount of time, in milliseconds, elapsed from when transactions are applied at the Publisher to when they are delivered to the Distributor.

MilliSec

Ideally, the value of this measure should be 0 or very low. A high value indicates that the Log Reader agent is taking too much time to copy transactions to the Distributor.

The possible causes for this are as follows:

  • A large number of replicated transactions in the transaction log;
  • A large number of non-replication transactions;
  • A large number of virtual log files (VLFs)
  • Slow disk subsystem
  • Unexpected network I/O problems
  • Blocking by another SQL Replication Agent such as a the Distribution Cleanup Agent