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,
- Replication components are not installed on this server-Error while adding subscriber in replication
- 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 problemWe 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.
SolutionWe 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!
