The row was not found at the Subscriber when applying the replicated command - Replication error in SQL Server by serverku

Earlier I wrote a post for the same error at Review of some replication issues and workaround, but it is just overview with some other replication errors. Please read it if you have not visited those posts and hope you will enjoy and help you much. Let me elaborate the error with proper example. For example, I have already created objects and configured replication, just need to use those objects. Let me introduce them,

• Primary database : Test
• Secondary database : Test1
• Replicated table : dbo.sample1

I am using the same server in the example as publisher, distributor and subscriber. First need to check records in tables of both databases.

-- Selecting  records from table of publisher database 
SELECT * 
FROM   test.dbo.sample1 (nolock) 

-- Selecting  records from table of subscriber database 
SELECT * 
FROM   test1.dbo.sample1 (nolock) 

How to raise an error manually?
To raise mention error in tile I will remove one record from a table in subscriber database to create inconsistency, then will update same deleted records and insert one more record in table of publisher database. Let us see what will happen then,

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

After running above script we will review replication monitor window and you will see error because it is trying update row at the subscriber side, but it is not exist and therefore it will raise an error. Due to this, new inserted records (id = 4) will not populate at subscriber side.

Solution 
Now we have to do some workaround to get it resolved, but first we need to get missing row details which was deleted at subscriber side. You can see sequence number in the above image from which we can get the same information. Run following script in distribution database and see the output,

USE distribution 

go 

SELECT * 
FROM   dbo.msarticles m 
WHERE  EXISTS (SELECT mc.article_id 
               FROM   msrepl_commands mc 
               WHERE  mc.xact_seqno = 0x0000002200000048000300000000 
                      AND mc.article_id = m.article_id) 

EXEC Sp_browsereplcmds 
  @xact_seqno_start = '0x0000002200000048000300000000', 
  @xact_seqno_end = '0x0000002200000048000300000000' 

(Click on image to enlarge)
The result is clearly showing article and missing row details. Let me apply that missing row at subscriber side and then review replication monitor again.

-- Inserting missing record in table in subscriber database 
INSERT test1.dbo.sample1 
SELECT 2, 
       'test2' 

After inserting above record the issue will get resolved, which you can see in the image below. Missing record and the newly inserted record (id = 4) also applied,

 Hope you enjoyed this post and it will be fine you put your innovative ideas here for any alternative solution and opinion

Replicated transactions are waiting for next Log backup or for mirroring partner to catch up - Issue in SQL Server Replication by serverku

Hope you read my earlier post of "Replication components are not installed on this server" issue, you may like it. One day suddenly replication went to high latency and I clicked it during monitoring. I opened the replication monitor, during analysis I found one message which was showing latency from publisher to distributor due to some issue. The message is as follows,

“Replicated transactions are waiting for next Log backup or for mirroring partner to catch up”.

As per message i checked the transaction log backups were happened or not, checked it and log backups were happening . Finally one option is pending to review and it is mirroring. I checked the status of mirroring  and see the  principal database went to synchronizing mode. May be mirroring went in synchronizing mode due to heavy or so many transactions in the route to apply at mirror database. So replication is waiting  to be synchronized status of principal database. I exactly do not know why replication went on waiting even transaction log backups were happening. I checked online solution and received some of the solutions from here and it suggests following,

1. EXEC sp_replicationdboption 'PublisherDB','sync with backup',false
This means that not need to backed up of all transactions before being delivered to the distribution database. Please visit this option here, which sets a replication database option for the specified database. This stored procedure is executed at the Publisher or Subscriber on any database.

2. Enable trace flag 1448
After this setting the Log Reader Agent can continue replicating changes regardless of the mirroring state. Please read more here.

I never applied these suggestions. Replication and mirroring both are using transaction logs and this could be the reason why replication was in high delay status while mirroring was stuck or in process to synchronize the mirror database. So I turned off mirroring and monitored replication status. Finally replication was succeeded to remove the delay and applied all pending commands. Then I configured to mirror again after doing with replication sync.

Conclusion : We have two options, either to wait for synchronized status of principal database or turned of mirroring, getting replication synced properly and reconfigure mirroring again depends on priority and importance. It will be better to go for correct solution to avoid such issue. But question is here, why replication went on waiting for a synchronized of mirroring even transactions log backups happened while? I would like you to share if received such issue and solution or any opinion which you applied to resolve it. This may help to me and all people facing the same issue.