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

/* 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. 

</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

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

Advertisements