Thursday 30 June 2016

Error 20598: The row was not found at the Subscriber when applying the replicated command

Having transactional replication environments with read-only subscribers (which means that changes are not propagated back to the publisher), it is very important to understand that rows on subscribers must NOT be modified directly. Otherwise, we will get a big problem. Let me expand on what I am saying. For instance, if any row on the subscriber (which was replicated from publisher) is deleted and then when this same row on published is modified, the following error will be raised:
The row was not found at the Subscriber when applying the replicated command
Clearly, this issue is because the row to be updated on subscriber does not exist any longer while Distribution Agent is trying to propagate it. Therefore, there is a need to fix it as soon as possible to prevent the replication queue from growing so much. To solve this case, we must review the pending commands inside the 'distribution' database by using 'sp_browsereplcmds' in order to identify the affected transaction(s) and row(s), and then insert the missing row manually in the subscriber (or delete the command from queue, however, this recommendation can be taken into account only if someone deleted the row by mistake or you do not need it anymore).

Another technique we have is to use the 'SkipErrors' parameter which allows to skip errors of a certain type (for this issue the error number is 20598), which means that the affected transaction is simply ignored and skipped. Keep in mind that these sorts of error must be treated with extreme caution and a correct understanding of the situation.

That is all for now, let know any remark you may have. Thanks for reading.

No comments:

Post a Comment

Let me know any remarks or questions you may have. Please write down your name.

HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE