August 7, 2015 at 3:32 pm
We have transactional one-way replication running, and suddenly today started getting this error :
The DELETE statement conflicted with the REFERENCE constraint "FK_BranchDetail_Branch".
The conflict occurred in database "LocationDB", table "dbo.BranchDetail", column 'BranchNumber'.
Message: The row was not found at the Subscriber when applying the replicated command.
Violation of PRIMARY KEY constraint 'PK_Branch'. Cannot insert duplicate key in object
'dbo.Branch'. The duplicate key value is (23456)
Disconnecting from Subscriber 'SQLDB03'
Publisher - SQLDB02.LocationDB
Subscriber - SQLDB03.LocationDB
Tables on both servers:
Branch (BranchNumber PrimaryKey)
BranchDetail (BranchNumber ForeignKey references previous table)
select * from SQLDB02.LocationDB.Branch -- contains : 23456,'Texas',...
select * from SQLDB03.LocationDB.Branch -- contains : 23456,'NULL',...
The problem is - the BranchNumber in question '23456' exists in all 4 tables (Publisher..Branch, Publisher..BranchDetail, Subscriber..Branch, Subscriber..BranchDetail).
Yet, when I ran a trace on Subscriber, I see repeated commands like:
exec [sp_MSdel_dboBranch] 23456 -- which throws FK violation
exec [sp_MSins_dboBranch] 23456,'NULL',... -- which throws PK violation
I'm guessing it's trying to Update the record on subscriber by doing a Delete + Insert. But it's unable to..
Users do not have access to modify Subscriber table. But they can modify Publisher table through UI, and have been doing so for long time without issue. There is also job that updates Publisher table once every night. We started getting this error around noon today.
Our last resort is to reinitialize subscription off-hours.
But any ideas what could have caused it and how to fix it?
August 7, 2015 at 4:25 pm
It sounds/looks like a command to update the subscriber failed at some point (network connectivity???) and left the second column blank on the subscriber.
That means the rows are no longer matching between the publisher and subscriber.
When replication tries to delete the row, it can't find one with 'Texas' in it and the row doesn't get deleted, then the insert fails because of the PK value already exists. There maybe more issues with the data, but that is what you showed us.
One suggestion is to update the subscriber row to match the publisher row. Then the commands should replicate.
This type of thing can also happen if the subscriber is accessible by users and someone runs a command on the subscriber thinking they are on the publisher.
-SQLBill
August 7, 2015 at 4:38 pm
Just found that - replication started failing immediately after an update job that ran at 9pm yesterday on Publisher.
The job runs every night, and it was never an issue all these days.
I think Replication is trying to delete based on Primary Key (Branch Number), not 'Texas' column, as evidenced by this command that showed up in trace on Subscriber:
exec [sp_MSdel_dboBranch] 23456
No users updated Subscriber.
The location value is first entered as NULL on Publisher during business hours, which replicates to Subscriber. Then the nightly job updates location on Publisher, which again replicates to Subscriber.
This has been going on for a long time. We need to figure out what exactly happened yesternight.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply