SQL Server Replication Troubleshooting: Common Causes of Latency and Failure

SQL Server transactional replication problems tend to cluster around a small set of causes. The difficulty is not usually identifying what is wrong in principle — it is knowing which component to look at first, and understanding why the error you are seeing is a symptom of an underlying condition rather than the condition itself.

This article covers the most common causes of replication latency and failure, and how to approach diagnosing each one.

How Transactional Replication Works

A brief summary of the mechanism is useful before troubleshooting, because understanding the flow makes the diagnosis logical rather than guesswork.

With transactional replication, changes on the publisher are first written to the transaction log, as they would be for any database transaction. The Log Reader Agent runs continuously, reading committed transactions from the publisher's transaction log that are marked for replication, and writing them into the distribution database. The Distribution Agent reads from the distribution database and applies those changes to the subscriber.

Latency can accumulate at either stage: between the publisher log and the distribution database (Log Reader throughput), or between the distribution database and the subscriber (Distribution Agent throughput). These are different problems with different causes.

Latency vs Failure: Different Problems

Latency means changes are being replicated but are arriving at the subscriber later than expected. The pipeline is working, but slowly. Failure means the agents have stopped — errors are occurring that prevent replication from proceeding at all.

Start by determining which you have. Replication Monitor (accessible through SSMS) shows the status of each agent and current latency. If agents show as Running, you have a latency problem. If agents show as Error or have stopped, you have a failure problem. The investigation paths are different.

Common Causes of Latency

Log Reader throughput. The Log Reader Agent has to read every committed transaction from the publisher's transaction log, regardless of whether that transaction involved a replicated table. On a publisher with very high write volume — many inserts, updates and deletes per second — the Log Reader may not be able to keep pace. Very large transactions are particularly problematic: the Log Reader cannot commit a transaction to the distribution database until the entire transaction has been read from the log, so a single large batch can cause a visible latency spike.

Check the Log Reader Agent history in Replication Monitor. Look at how many commands it is delivering per second and compare against the publisher's write volume. If the publisher is generating significantly more changes than the Log Reader is delivering, throughput is the bottleneck.

Distribution database not being maintained. The distribution database is a real SQL Server database with tables that accumulate data. The Distribution Agent uses it as a queue. If the distribution database is not being cleaned up regularly — old replicated transactions purged once delivered — it grows over time and performance degrades. The cleanup job is configured when distribution is set up, but it is sometimes set too conservatively or not running reliably.

Check the size of the distribution database and the MSrepl_commands and MSrepl_transactions tables within it. Check whether the cleanup agent job is running and succeeding on schedule.

Subscriber performance. The Distribution Agent applies changes to the subscriber. If the subscriber database is slow to apply changes — due to indexes on the subscriber that don't exist on the publisher, triggers on subscriber tables, or resource contention on the subscriber server — latency accumulates at the delivery end rather than the publishing end. Replication Monitor shows latency figures for each subscription; if the Log Reader is keeping pace but subscriber latency is growing, the delivery side is the bottleneck.

Network latency. If the distributor and subscriber are in different physical locations or connected over a slow network link, the delivery of changes is constrained by that link. This is typically only relevant where replication crosses a network boundary.

Common Causes of Failure

Distribution Agent errors. The Distribution Agent stops when it encounters an error it cannot continue past. Common causes include constraint violations on the subscriber (a row being inserted that already exists, or a row being updated that doesn't exist — indicating subscriber drift), permission errors, and connectivity problems. The error details are in Replication Monitor, not in the SQL Agent job log. Many teams look at the Agent job history and see a generic failure message; the actual error is one level deeper in the replication agent history.

Schema changes on published tables. Making a schema change to a published table — adding a column, changing a data type, adding an index — without following the correct replication-aware process is one of the most reliable ways to break replication. The correct process is to use sp_repladdcolumn or replicate the DDL change through the publication. A schema change made directly with ALTER TABLE will break the replication agents. The error message may not be immediately obvious about the cause.

Subscriber drift. The subscriber is supposed to be an exact mirror of the published tables on the publisher. If rows are modified directly on the subscriber — either by an application, by a direct query run by a DBA, or by a failed replication delivery that left things in a partially applied state — the subscriber diverges from the publisher. When replication subsequently tries to apply a change to a row that the subscriber has in a different state, it fails with a constraint or row-not-found error. This is subscriber drift, and it requires reconciliation — either manually correcting the diverged rows or reinitialising the subscription.

Transaction log not being cleared. With transactional replication enabled, SQL Server will not clear log records until the Log Reader Agent has read them, regardless of backup schedule or recovery model. If the Log Reader Agent is stopped or falling significantly behind, the transaction log on the publisher will grow — sometimes to the point of filling the disk. This is not a replication failure in itself, but it causes one when the log cannot grow further. The solution is to resolve whatever is preventing the Log Reader from running, not to clear the log by other means.

Using Replication Monitor Effectively

Replication Monitor is the primary diagnostic tool for replication problems and is significantly more informative than checking SQL Agent job status. Access it through SSMS by right-clicking the Replication node in Object Explorer, or from the publisher server.

The key information it provides: current status of each agent (running, error, stopped), latency for each subscription shown as a tracer token, and the full history of each agent's activity with specific error messages. The agent history view is where the actual error message lives when an agent fails — not in the SQL Agent job history.

For latency diagnosis, insert a tracer token through Replication Monitor and observe how long it takes to travel from publisher to distributor to subscriber. This isolates which stage is introducing the delay.

Key System Tables to Query

Within the distribution database:

• MSrepl_errors — errors encountered by replication agents, with error text and timestamp
• MSdistribution_history — history of Distribution Agent activity, including commands delivered per second
• MSrepl_commands — pending commands in the distribution queue; high row counts indicate a backlog

When to Reinitialise vs When to Fix the Underlying Problem

Reinitialisation — taking a new snapshot and re-applying it to the subscriber — resolves subscriber drift and some failure conditions. It is the right answer when the subscriber data is known to be diverged and reconciliation is more work than a fresh start.

It is not the right answer when the underlying cause is a schema change made incorrectly, a Log Reader throughput problem, or a distribution database maintenance issue. Those problems will recur after reinitialisation. Fix the cause first; reinitialise only when the subscriber data itself is the problem.