We ran into an issue in our pre-prod environment last week where transnational replication wasn’t processing any data changes from publisher to subscriber.
Looking at Replication monitor we had a error similar to this:
TranRep01
Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x00000024000002F0000400000000, Command ID: 1)
Error messages:
Violation of PRIMARY KEY constraint ‘PK__t1__3213E83FBA2CFEA2’. Cannot insert duplicate key in object ‘dbo.t1’. The duplicate key value is (3). (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627
Violation of PRIMARY KEY constraint ‘PK__t1__3213E83FBA2CFEA2’. Cannot insert duplicate key in object ‘dbo.t1’. The duplicate key value is (3). (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627

and you can see the outstanding commads in the “Undistributed Commands”
TranRep00

The problem is that a table on the subscriber has been updated or inserted into data that conflicts with what the publisher has. the fix is pretty simple.
First copy the seq_no from the error and paste it into the below query
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/* Copy the seq_no from Replication Monitor  */
-- 0x00000024000002F0000400000000
 
/* QUERY 1 */
/* Gather information on Primary Key constraint error */
SELECT  art.publisher_id ,
        art.publisher_db ,
        art.publication_id ,
        art.article ,
        art.article_id  ,
        art.destination_object  ,
        art.source_owner  ,
        art.source_object
FROM    distribution.dbo.MSarticles AS art
        JOIN distribution.dbo.MSrepl_commands AS com
        ON art.Article_id = com.Article_id
WHERE   com.xact_seqno = 0x00000024000002F0000400000000
 
 
</pre>
<pre>
Which results in:
TranRep02
From this we can get the rest of the information we need to run sp_browsereplcmds.
1
2
3
4
5
6
7
8
</pre>
<pre>/* Populate with details from Query 1*/
EXEC distribution.dbo.sp_browsereplcmds
     @xact_seqno_start = '0x00000024000002F0000400000000' ,
     @xact_seqno_end = '0x00000024000002F0000400000000' ,
     @publisher_database_id = 1 ,
     @article_id = 1 ,
     @command_id = 1
This returns what we need to resolve the issue
TranRep03
The Command Column
{CALL [sp_MSins_dbot1] (3,’insert pub’)}

From here we can query the subscriber table and delete the row with ID 3 that has data not matching the publisher.  (In my test example you can see i manually entered a record called “insert sub” with an ID 3 into the subscriber, which conflicts with the publisher row which is ID 3 data “insert pub”.
TranRep04
So to resolve the issue we simply need to delete the the subscriber row
1
2
3
4
5
USE repSub
GO
 
DELETE FROM t1
WHERE id = 3
TranRep05
After deleting the row, you just need to wait for replication to sync, and you will see the “Undistributed Commands” go down to 0 again and the “Distributor To Subscriber History” should show it as Running.
TranRep06
TranRep07