2

I've setup a transactional replication and allowed initialize from backup due to bandwidth restraints (so no snapshots generated). Now we get this error on the Publication database, when the application that inserts data in the database is started (well, it runs for a short time, then it stops with the error). The replication monitor shows no errors, so it's not an inconsistency between publication and subscription database. Nor is it a concern about autogenerated IDs (i.e. using an Identity column), as this table relies on datestamp as PK. The error is:

System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint ''. Cannot insert duplicate key in object 'dbo.'. The statement has been terminated.

Can the replication setup have done something to the source/publication database causing this? Can this be caused by a high latency to the subscriber database? As I don't see errors in the replication monitor, I'm not even certain that this error occured in continuation of the replication setup.

How do I troubleshoot this?

Pac0
  • 21,465
  • 8
  • 65
  • 74
Bo Andersen
  • 21
  • 2

1 Answers1

0

1. Make sure the table/article you are replicating is created with replication setting in the PK

Before: 

CREATE TABLE [dbo].[mytable](
    [mytableID] [BIGINT] IDENTITY(1,1)  NOT NULL,

After: 

CREATE TABLE [dbo].[mytable](
    [mytableID] [BIGINT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

You have to do the same for Foreign keys as well

2. Replication monitor should show you the error message

  • Select the publication.
  • Select subscription on the right window and subscription window will pop up.
  • Select the Distributor to Subscriber History (may not be available on pull subscription types, then you need to go the subscribing servers).
  • This tab will display Distribution Agent transactions. You can change the agent profile parameter "HistoryVerboseLevel" to 2 to get more details. If you do that, remember to stop and start the distribution agent to see the changes. This can be done without the risk of losing replication data.
xlm
  • 6,854
  • 14
  • 53
  • 55
Felix S.
  • 91
  • 6