SQL Replication Error 20589 & Violation of PRIMARY KEY constraint 'xxx'

Question

Our prod box is replicating the data to Business intelligence Server to prepare business related reports and Bi team is highly rely on this data. For some time we see some 20589 (The row was not found at the Subscriber when applying the replicated UPDATE/DELETE command for Table) and Violation of PRIMARY KEY constraint errors. To make sure the replication is successful for remaining tables we skip those errors. But now BI team reported some data missing errors and due to this their reports are inaccurate. This is business critical issue and we need your help to resolve the issue.

Publisher Version – Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64) Jun 12 2020 20:39:00 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Replication Type – Transactional Replication (Pull)

Distributor Version - Microsoft SQL Server 2016 (SP2-CU13) (KB4549825) - 13.0.5820.21 (X64) May 22 2020 07:24:35 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Answer 1

The potential reasons I can see for this happening are:

You have modified the data on the subscriber side. Don't do that.

You have different collations on the subscriber and the receiver. Imagine one table/column for instance being case sensitive and the other being case insensitive. On the cse sensitive 'Nithin' = 'nithin' would be false and the on the other it would be true.

Answer 2

Hi @Nithin Pallo Kavil ,

A Distributor can be any version as long as it is greater than or equal to the Publisher version (in many cases the Distributor is the same instance as the Publisher). Suggest you upgrade your distributor version. Please refer to Upgrade or patch replicated databases to get more detail information.

> For some time we see some 20589 (The row was not found at the Subscriber when applying the replicated UPDATE/DELETE command for Table)

Error 20598 refers to a missing row on the subscriber side, and there are two scenarios that can cause this error:

1.An UPDATE command cannot be replicated because there is no record that matches the update condition on the subscriber side.
2.A DELETE command cannot be replicated because there is no record that matches the delete condition on the subscriber side.

We can using replication monitor to find the missing data and manually update the missing data in subscriber. Please refer to below blogs to get detail steps.

Auto Fix SQL Server Transactional Replication Error 20598 for UPDATEs
Troubleshoot error 20598 in transactional replication

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.