Transactional Replication – Violation of Primary Key Constraint

Recently, I encountered a case where the
Transactional Replication between our Production OLTP server named XYZ
and Reporting database server named PQR went out of sync. Both
the servers were located in the same Data Centre in the USA. The Reporting database was used by the users in Canada for fetching
the data from the SSRS reports. At 06:30 AM IST, I was notified regarding this
issue. Canada Business hours normally start at around 1800 hrs IST. Considering
the fact that I have around 12 hours to fix this issue, my main aim was to fix
it not by setting up the Replication procedure again but to do a deep
investigation and find an alternative and a convenient way to fix it.

I started by opening the
Replication Monitor, the error message was

Violation of Primary Key Constraint in the table ABC

Just FYI, the similar
error message would also be shown in the View Synchronization Agent
tab . In the Replication Monitor along with the error
message, I noticed that the command_id and xact_seqno for the culprit command
was 1 and 0x00040C1F00002931001900000000 respectively.

Based upon the command_id and xact_seqno as
discussed above, my next aim was to find the exact command due to which the
Replication procedure was failing.

Against the distribution database on the
publisher instance, I executed the below T-SQL to find the article id and the
publication database id (in the Replication topology each replicated
table is referred as an article).

select *
 from msrepl_commands (nolock) 

 where command_id = 1 and xact_seqno =

Once the above T-SQL query was executed, I received the
corresponding database id in the output message as 3 and the article id of 2291.

The next step was to find the
exact table name which was having an issue on the subscriber. In order to do
that, I executed the below T-SQL query against the database nameddistribution on the Production OLTP Server (Publisher).

select * from dbo.MSarticles 
 where article_id IN (SELECT Article_id from

 where xact_seqno = 0x00040C1F00002931001900000000)

 and publication_id =3

xact_seqnoandpublication_idis the one which we
have obtained by executing the T-SQL commands as discussed above which
were 0x00040C1F000029310019 and 3 respectively.

When the above T-SQL query was executed, the output message showed the table name to be astbl_ABC. At this stage, I
was sure thattbl_ABC was the table which was having an issue.

After identifying the table name the next challenge was to
find the Replicated command which was having an issue to get propagated onto
the subscriber.

In order to find the Replicated command, I executed the below
T-SQL query against the distribution database on the Production OLTP Server.







Each and every input
parameter specified in the sp_browsereplcmdssystem stored procedure
has been obtained from the steps which we have discussed above. As soon as the
above T-SQL query was executed, I received the following command:

{CALL [sp_MSIns_dbotbl_ABC] (91962)}

 Once the above T-SQL was
executed, I noticed a very strange thing. One of the records in the table named
tbl_ABC  which was already present on the subscriber was present on the
publisher as well and the system was trying to re-enter the same entry into the
table again on the subscriber.

Select * from tbl_abc where LogID=91962

Where LogID was
the Primary Key column.

On the
subscriber, I decided to delete this particular record. Before deleting the
record, I took the backup of the table on the subscriber as,

Select * into tbl_abc_backup_20130830 from tbl_abc

Once the backup of the table was taken
successfully after executing the above command, I deleted the particular record
from the table on the subscriber as below.

delete from tbl_abc where LogID=91962

Once the above T-SQL was executed the
Transactional Replication procedure started working fine again. In the View
Synchronization Status
tab present under the Replication tab in the
Object Explorer, I noticed that the Replicated Commands were being delivered

This was one such approach that we used to
ensure that we brought the Replication Procedure on track successfully before
the business started. I have seen many SQL DBA’s preferring setting up the
entire procedure again but that is not an ideal approach especially when the
database size is huge and setting up Replication takes a long long time. Please
do let me know if you have any alternative solution/suggestions regarding the


3 Responses to “Transactional Replication – Violation of Primary Key Constraint”

  1. Dear Satanam..Thanks Post….

    I faced this issue several time whenever monitored our PEER_TO_PEER replication and noticed that the violated primary key Id is already replicated to the subscription database..

    What I follows is just skip the primary key violation error from distributor properties..But some risk is behind this..

  2. I know this post is a little old, but I am encountering the same issue with PK violations. From what I understand to this point, is that this is a problem in horizontal filtering in SQL Server. UPDATE statements in the publisher are replicated as INSERT/DELETE statements. I am researching this issue and how to change that behavior, due to key violations when an insert is attempted with a key value that is already present in the subscriber database. I don’t fully understand yet why this occurs, as it would surely violate PK constraints on every UPDATE statement. I hope to understand this behavior soon, as it is making managing horizontally filtered publications far too laborious to be worth it.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |