0

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?

d-_-b
  • 761
  • 2
  • 11
  • 26
  • I've seen this in the past and 99% of the time it was because someone with admin level powers on the subscriber accidentally ran an update on the tables (and didn't own up to it). If you can find the offending row on the subscriber, and "fix" it to match the publisher, often times a re-initialization isn't required, and SQL will sort it out (after you fix the out-of-sync row). It could be they changed a value on an existing row, or added a new one... anything to put the tables out of sync. Sometimes re-initializing is the simplest way though. – Dave C Aug 07 '15 at 21:36

1 Answers1

0

For transactional replication, updating primary key column is replicated as delete + insert (deferred update). because this PK column has an FK constraint, the delete will fail at the subscriber. You have a couple workarounds to prevent this from happening moving forward:

  • Disable replicating of FK constraints, as it is not really needed for one-way replication. Why? Users are not entering data at the subscriber, so there is no need to maintain referential integrity, and transactional Replication replicates log txns, so the order of txns is pretty much guaranteed, no need to worry about one txn showing up before another.
  • Enable trace flag 8207 on the publisher. If only a single row is updated, then it will be replicated as a single UPDATE statement. If the update affects multiple rows, then it will be replicated as deferred update.
  • Somehow block users from updating PKs

IMO, best bet is the first option.

How to fix this? Reinit is one way. But if you can manaully disable or drop the FK constraint on the subscriber, that is easiest solution.

Greg
  • 3,861
  • 3
  • 23
  • 58