SQL DBA : How to fix "Primary Key Violation " error in SQL Server transaction replication .

Error messages:

Violation of PRIMARY KEY constraint 'PK_tablename'. Cannot insert duplicate key in object 'dbo.tablename'. (Source: MSSQLServer, Error number: 2627)

Impact: 

This type of error generally appears in Transaction replication type.

Replication sync gets fail if this error reported. data\transaction sends from publisher to distributor. But from distributor to subscriber it fails and report the error in transactional replication monitor.

  

Where to find:

> Go to replication monitor > All subscriptions column  > Status column > double click > check the information from publisher to distributor and dist> sub

>  You can see error of primary key violation in dist to sub history.

From the error note down Transaction sequence no & Command ID

Solution:

1. First find what transaction is inserted into subscriber which is causing this error by using below commands and execute only on distribution database.

EXEC Distribution. Sp_browsereplcmds

 @xact_seqno_start = ‘0x00000018000000A1000300000000’,

@xact_seqno_end = '0x00000018000000A1000300000000',

@command_id =1, @publisher_database_id = 1

2. COMMAND with transaction: {CALL [sp_MSins_dboetab] (N'3    ', N’c      ', N’ap        ')}

3. Delete manually from subscriber and monitor for some time.

4. Now replication should work with excellent performance with no latency value.