Replication Issue – Duplication of Primary Key

Replication Issue – Duplication of Primary Key.

Replication synchronizes data and article on subscriber same as it is on publisher.

Changing and inserting data directly at subscriber cause to break replication.

Please look the following error.

Error Message

Command attempted:

 

if @@trancount > 0 rollback tran
(Transaction sequence number: 0x00000031000001B1000500000000, Command ID: 1)
Error messages:
Violation of PRIMARY KEY constraint ‘PK__Replicat__3214EC27323EC80A’. Cannot insert duplicate key in object ‘dbo.ReplicationTest’. The duplicate key value is (2). (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627
Violation of PRIMARY KEY constraint ‘PK__Replicat__3214EC27323EC80A’. Cannot insert duplicate key in object ‘dbo.ReplicationTest’. The duplicate key value is (2). (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627

Replication1

The problem occurs by inserting duplicate key in dbo.ReplicationTest.

Step1 : Track the problem using T-SQL Query.

Copy transaction sequence number and assign to xact_seqno.

The result will show you publisher, publisher db, source article, destination article.

USE [Distribution]
Go

SELECT
  MSA.*
FROM
  dbo.MSarticles AS MSA
JOIN   MSrepl_commands AS MSRC
  ON MSA.Article_id = MSRC.Article_id
WHERE
  MSRC.xact_seqno = 0x00000031000001B1000500000000

Replication2
Step2 : To get the exact row which try to insert data in subscriber.

You have to use SP_BrowseReplCmds procedure of distribution database  and need to pass transaction sequence number to @xact_seqno_start parameter.

EXEC Distribution.DBO.Sp_browsereplcmds
@xact_seqno_start = ’0x00000031000001B1000500000000′

Replication3

See the column “Command”  it will shows you full query.

Error on procedure SP_MSins_DBOReplicationTest with parameter “2″ and “Test” is failing, it means ID “2″ is already exist on subscriber table.

You have a two options to solve, you can modify the record at subscriber or delete the record from subscriber table.

Regards,

Nirav Gajjar