Skip distributor agent error in sql server transactional replication by serverku

A while before few weeks, I discussed about an error of replication “The row was not found at the Subscriber when applying the replicated command”. We had a trick to get discrepancies for error table and resolved the issue. Let me put another method (Actually patch) to come out from same error which is very interesting. Before moving this method, Please read some my posts related to replication which you may like,

1. Replication components are not installed on this server-Error while adding subscriber in replication
2. Replicated transactions are waiting for next Log backup or for mirroring partner to catch up-Issue in SQL Server Replication

Now going to move ahead, here I am talking about the solution for the above highlighted error while is “Skip error in sql server transactional replication, How to? ”.

Raise a problem
We will consider the same objects created and same scenario for a transactional replication in this post. Let us check the data of ready made tables,

SELECT * 
FROM   test.dbo.sample1 (nolock) 

SELECT * 
FROM   test1.dbo.sample1 (nolock) 

We have the same records in both tables at the publisher and subscriber side. Now it is time to create discrepancies using following script,

-- Deleting one record from table in subscriber database 
DELETE FROM test1.dbo.sample1 
WHERE  id = 2 

-- Updating same record from table in publisher database 
UPDATE test.dbo.sample1 
SET    name = 'test5' 
WHERE  id = 2 

-- Inserting new record in table in publisher database 
INSERT test.dbo.sample1 
SELECT 4, 
       'test4' 

Monitoring replication after the above script ran,

And viewing table’s data again from the publisher and subscriber after an error,

You can see nothing happened at subscriber because of an error occurred for one missing row.

Solution
We are about to skip this error where all replication commands stuck, So sp_setsubscriptionxactseqno system stored procedure help us which is used to troubleshooting to specify the log sequence number (LSN) of the next transaction to be applied by the Distribution Agent at the Subscriber. So let us first get sequence number of an error which we have from the replication monitor. We can also use following script to get all transactional replication errors and executed in distributor server in a distributed database,

USE distribution 
GO

DECLARE @PublisherServer  VARCHAR(50), 
        @PublicationDB    VARCHAR(50), 
        @SubscriberServer VARCHAR(50), 
        @SubscriberDB     VARCHAR(50), 
        @PublicationName  VARCHAR(50) 

SET @PublisherServer = '<Publisher>' 
SET @PublicationDB = 'test' 
SET @SubscriberServer = '<Subscriber>' 
SET @SubscriberDB = 'test1' 
SET @PublicationName = 'testpub' 

EXEC Sp_helpsubscriptionerrors 
  @PublisherServer, 
  @PublicationDB, 
  @PublicationName, 
  @SubscriberServer, 
  @SubscriberDB 

GO 

(Click on image to enlarge)
Please get a top sequence number of an error from where all transaction commands stuck and run following script on the subscriber side,

USE test1 
GO

DECLARE @PublisherServer VARCHAR(50), 
        @PublicationDB   VARCHAR(50), 
        @PublicationName VARCHAR(50) 

SET @PublisherServer = '<Publisher>'
SET @PublicationDB = 'test' 
SET @PublicationName = 'testpub' 

EXEC Sp_setsubscriptionxactseqno 
  @PublisherServer, 
  @PublicationDB, 
  @PublicationName, 
  0x00000022000000C1000400000000 

GO

Finally running above script in the subscriber database, this error skipped and all remaining and pending commands were applied, which we can see the data of both tables from the publisher and subscriber database,

This is just my experience which I am sharing with you. It is recommended to find a route of this error and solve it. Did you receive such error and you skipped any? Please share your thoughts!