Transactional replication to our reporting subscriber has gone wrong, latency climbing for days and now the distribution agent errors with row not found at the subscriber.
How do I diagnose where replication is stuck and what are the repair options short of reinitializing everything?
Two distinct problems arrived together, which is typical: latency says the pipeline is behind, the row not found error says the pipeline is also poisoned. Diagnosis locates the blockage, then the repair menu runs from surgical to nuclear.
Locate first: Replication Monitor from SSMS shows both agents per subscription, the Log Reader moving commands from the publisher log to distribution and the Distribution Agent moving them to the subscriber and which one's latency climbs names the stuck half. A tracer token, inserted from the monitor, times both legs precisely. Days of climb with a distribution agent error means commands piled in distribution behind the failing one, countable with a query on distribution.dbo.MSdistribution_status for the undelivered backlog.
The poison itself, row not found, means the subscriber lost a row the publisher later updated or deleted, someone or something wrote directly to the reporting database, the usual confession. The surgical repairs in order: identify the failing command with sp_browsereplcmds filtered near the error's transaction sequence number to see exactly which row is missing, then either insert that row at the subscriber by hand so the pending command lands, or, when the misses are many but enumerable, the sp_scriptdynamicupdproc style approaches lose to simply accepting skip and repair: setting the distribution agent profile to continue on data consistency errors clears the backlog past the poison while logging every skip, followed by tablediff run per affected table to generate the exact fix script reconciling subscriber to publisher. That pair, skip profile plus tablediff, repairs most poisoned subscribers without reinitialization.
Reinitialize when honesty demands it: subscriber drift too wide for tablediff patching, or repair time exceeding what a fresh start costs. Reinitialization from snapshot locks the publisher tables briefly during generation, initialize from backup avoids that for large publications. Afterwards, close the door the poison came through: the reporting subscriber gets read only access for its users, direct writes being how this thread gets written and a weekly tablediff in a job catches any future drift while it is still one row instead of days of backlog.
A cleanup script on the reporting box had been deleting rows for weeks, confession found in its own log. Skip profile cleared the backlog overnight, tablediff scripts reconciled 800 rows and the cleanup script now runs nowhere near replicated tables. Latency back to seconds.