1

For a long time all was ok. But now (couple of weeks, maybe) I am getting this weird errors in replication monitor:

  • One DB has error 2627 (Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.).

  • Another one getting error 20598 (The row was not found at the Subscriber when applying the replicated command).

We have one publisher+distributor and one subscriber.

So every evening I create new snapshot and reinitialize subscription (and it shows that it's ok) and every morning I see same errors. The problem is I cannot understand why is it happening. As I understand replication when I take snapshot it delivers current state database to subscriber and transactions which happened after snapshot has been made. The record in the first case is usually 1-2 days old so I think it should be within snapshot and delivered with it. The subscriber updated only by distribution agent, no one accessing it otherwise.

I've read advice about using sp_browsereplcmds for second error but it merely says something about wrong syntax at "∢", whatever that could mean. Could you advice me what should I do to get it working again, please?

user2838376
  • 149
  • 2
  • 11
  • 1
    Is there any way something else is inserting into the subscribing table? If the publishing table has an identity as "not for replication", then the identity values will be preserved at the subscriber but separate inserts at the subscriber will just take on the next available identity value. So you could get an identity collision. Are you using identity columns and if so are they using "not for replication"? – DragonMoon Aug 12 '14 at 05:32
  • +1 on the "Is there any way something else is inserting into the subscribing table?". – Brandon Williams Aug 12 '14 at 05:48
  • Regarding the row not found error, is there anyway something else is deleting from the subscribing table? – Brandon Williams Aug 12 '14 at 05:49
  • I don't think user software could be inserting or deleting records since it required recompiling software for a new server, which we never done before for this subscriber. And I cannot imagine what else could it be.. If nothing else, I will try to recreate publications and subscriptions for them later this evening. – user2838376 Aug 12 '14 at 08:14
  • It's been over 3 years since the original post, but I was wondering if there have been any further findings. We're currently facing the same issue with MS SQL 2016, and the error (key violation) occurs on random tables at random times, resulting in replication getting halted. Nothing in our application would be writing into the subscriber DB, and if it was doing that the errors would be occurring consistently. I'm not privy to the internals of the transactional replication engine in MS SQL, but all signs are pointing at a potential flaw causing records to be replicated/re-tried twice. – Alex Lobakov Oct 10 '17 at 16:05
  • No, we were not able to find solution. We switched to different failover model using VM replication instead. – user2838376 Mar 29 '18 at 03:05

0 Answers0