Error 20598: The Row Was Not Found at the Subscriber When Applying Commands

Fix it now

Error 20598 is published as the row not being found at the Subscriber when applying a replicated command for a named table, with the primary keys printed in the message. Transactional replication ships commands rather than rows, so a command that targets a missing row has nowhere to land. The fix is to find out how the copies drifted, not to skip errors forever.

USE distribution;
EXEC sp_browsereplcmds
     @xact_seqno_start = '0x0000000000000000',
     @xact_seqno_end   = '0xFFFFFFFFFFFFFFFF';

1. Open Replication Monitor, find the failing subscription and read the error detail. It gives you the transaction sequence number and the command id, and the 20598 message itself prints the table and the primary key values.
2. Narrow the call above to that sequence number range and match the command_id column to the command id from the error. That gives you the exact statement the agent is trying to apply.
3. Query the Subscriber for that key and confirm the row really is absent, then query the Publisher for the same key so you know what should be there.
4. Ask why it is absent before doing anything: a local purge job, a direct write, or an initialisation that did not match are the three usual answers.
5. Reconcile properly, by validating the article and reinitialising just that article, rather than leaving the agent skipping errors indefinitely.

Microsoft’s documented quick remedy for a single known row is to insert it at the Subscriber so the Distribution Agent can retry the failed command. That is first aid: it does not tell you why the row went missing.

If the agent runs clean and validation agrees, stop here. If it fails again next week, the next section covers where the drift comes from.

Why it happens

The Log Reader Agent turns committed changes at the Publisher into commands, and the Distribution Agent replays them at the Subscriber. Nothing in that pipeline compares the two copies as it goes. An update or a delete is applied by key, so if the row with that key is not there, the command has nowhere to land and the agent stops. The message names the table and prints the primary keys, which is more help than most replication errors give you.

Drift comes from a small number of places. Somebody writes directly at the Subscriber, deliberately or through an application nobody remembered was pointed there. A retention or purge job runs locally and removes rows replication still expects. The subscription was initialised from a backup or by hand and the starting point did not match. Or errors were skipped in the past, so commands were dropped and every later command that depended on them now fails.

Error 2601 is the same problem seen from the other end. Its published text is that a duplicate key row cannot be inserted into the named object with the named unique index, and it prints the duplicate key value. An insert arrives for a key that already exists at the Subscriber and the unique index rejects it. When you see 20598 and 2601 alternating in one subscription, treat it as a single drift problem rather than two.

One more thing is worth knowing before you start changing data: the two copies can be compared properly. The tablediff utility is current, ships with the replication feature, and is documented as comparing rows between a source table at a Publisher and destination tables at Subscribers, with the option to generate a Transact-SQL script that brings the destination into convergence. It is a better starting point than reading rows by eye.

Full reference

Reading the evidence

Finding the command behind the error

1. Take the transaction sequence number and command id from the agent’s error detail in Replication Monitor.
2. Run sp_browsereplcmds at the Distributor, on the distribution database, narrowed to that sequence range.
3. Match the command_id column in the results to the command id from the error.
4. Read the command column, which is the Transact-SQL the agent is trying to apply, and note the article_id.
5. Use that article id to identify the table at the Publisher and validate it.

Validation options, and what each costs

sp_publication_validation runs at the Publisher on the publication database and raises a validation request for every article in the publication. Run it before and after a reconciliation so you have a before-and-after rather than an impression.

tablediff, used properly

• It ships with the replication feature, and in SQL Server 2022 lives in the COM folder under the instance’s installation path.
• It can compare row counts and schema quickly, or go column by column.
• It can generate a Transact-SQL script that brings the destination into convergence, which is the output worth keeping.
• Read that script before you run it. It is a record of exactly how far apart the copies had drifted, and it is the argument for fixing the cause.

Telling the agent to continue past these errors does not repair anything. It discards the failed commands permanently, so the Subscriber falls further behind with every skip and the eventual reconciliation gets larger. Microsoft documents the skip parameter as a way to keep the rest of the changes flowing while you deal with the referential integrity question, not as a standing configuration.

Every code this article covers

Confirm the fix worked

1. Article or publication validation reports that row counts, and checksums if you used them, agree.
2. The Distribution Agent runs to completion with no errors in its history.
3. An insert, update and delete of a test row at the Publisher arrives at the Subscriber.
4. No error-skipping parameter remains on the agent profile.
5. Nothing other than replication holds write permission on the subscription database.

Questions people ask about this