Row was not found at the Subscriber

We got one replication related alert for one of the critical database. Distribution agent was failing with below error message:

The row was not found at the Subscriber when applying the replicated DELETE command for Table ‘[dbo].[TableXXX]’ with Primary Key(s): [PrimaryKeyID] = 12345678 (Source: MSSQLServer, Error number: 20598)

Above error could be seen in replication monitor. Distribution Agent was retrying. It kept failing again and again with same error message. Further commands from Publisher were getting piled up and latency was increasing.

It was clear from above error message that command was trying to delete some row which was not available in subscriber table.

There were various options:

1. Although publication database was big in size(600 GB) but only few articles (20 GB size) were part of publication. Previous snapshot creation had taken around 10 minutes to create. Although, history logs to see time taken by distribution agent to apply this snapshot was not available. We always had the option to reinitialize subscription. But this should only be used as a last resort.
2. We can check with application team if they can fix the missing row at subscriber. But they might have to do it for multiple rows if further commands fails with similar error.
3. We can skip that particular transaction as it was a DELETE command. Anyways, skipping a delete command where row is already not present will not cause any data consistency issues. We used below approach to find that transaction and skip that.

Find command ID of failing transaction by running below command at distribution database:

sp_helpsubscriptionerrors ‘Publisher SQL SERVER NAME’,‘publication db name’,‘publication name’,‘Subscriber SQL SERVER NAME’,‘Subscriber db name’:

Command ID would be in format like 0x0000001A00000186000300000000

Skip above transaction by running below command at subscriber database:

sp_setsubscriptionxactseqno ‘Publisher SQL SERVER NAME’,‘publication db name’,‘publication name’,command_id

Fortunately, in this case skipping only one command resolved issue. Pending transactions started applying to subscriber database. There might be a situation where pending transactions also fail with similar error and need to be fixed in similar manner.

We hope it helps you in similar scenarios. Happy Learning !