Violation of PRIMARY KEY constraint 'PK_Sales_OrderLines'. Cannot insert duplicate key in object 'Sales.OrderLines'. The duplicate key value is (2). (Source: MSSQLServer, Error number: 2627)
Or
The row was not found at the Subscriber when applying the replicated DELETE command for Table '[Sales].[OrderLines]' with Primary Key(s): [OrderLineID] = 2 (Source: MSSQLServer, Error number: 20568)
The most common reasons for these errors I have found are: duplicate article subscription or another process modifying the data in the subscriber table.
Duplicate article subscription
To find out if it is due to duplicate article subscription, we can run in the publisher database (e.g. WideWorldImporters) the following stored procedure providing the subscriber instance name and the article name from the error message above (e.g. OrderLines)
exec sp_helpsubscription @subscriber = 'InstanceName', @article = 'OrderLines'
We will see that we are subscribing to the same article OrderLines from 2 publications BigTables and SmallTables
Thus a single insert in the table OrderLines in the publisher will be replicated twice to the subscriber generating the duplicate key error. In similar manner, a single delete in the table OrderLines in the publisher will be replicated twice to the subscriber generating the row not found error
To fix it
- Remove the article from one of the publications
- Validate the replicated data
- If data is not in synch, then reinitialize the subscription Or synch the data manually using tablediff
Another Process Modifying the Data
To find out if it is due to another process modifying the data we can create a SQL Audit on the subscriber table (e.g. OrderLines). We can filter by the account that have access or use public to show all accounts, as shown below.
USE [master] GO CREATE SERVER AUDIT [Audit-OrderLines] TO FILE ( FILEPATH = N'c:\temp\' ,MAXSIZE = 1024 MB ,MAX_FILES = 10 ,RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ,AUDIT_GUID = '146fab87-58c8-4521-b4b5-9ffb175f739a' ) ALTER SERVER AUDIT [Audit-OrderLines] WITH (STATE = ON) GO USE [WideWorldImportersDSS] GO CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-OrdeLines] FOR SERVER AUDIT [Audit-OrderLines] ADD (DELETE ON OBJECT::[Sales].[OrderLines] BY [public]), ADD (INSERT ON OBJECT::[Sales].[OrderLines] BY [public]), ADD (UPDATE ON OBJECT::[Sales].[OrderLines] BY [public]) WITH (STATE = ON) GO
The SQL Audit file will show who, when, and what modified records from the table. In the example below, the first line was a trigger, the second was a stored procedure, the third was an ad-hoc SQL, and the rest of the lines were done by the replication account
To fix it, we need to stop the other processes are modifying data and then resynch the data