Transaction Replication - Missing data in Target

Question

Transaction Replication - Missing data in Target

Gladias Mariadoss 0

Good day Team,

Source: SQL Server: DB Version: SQL-2019 CU-19 OS: Windows 2019
Target: SQL Server: DB Version: SQL-2022 OS: Windows 2022
Publisher and Distributor are the same as of Source Server
Subscriber is the Target mentioned above. After dropping all incoming data load to Source and dropping all connections, DB from source is backed up and restored in Target. No Snapshot has been created and Snapshot Agent has not been run so far as it is not required.
I have two publications in Source:
Publ1 - Inserts Only, Do Not Replicate Update Do Not Replicate Delete
Publ2 - Inserts, Updates and Deletes
Problem Description - Publ1 - I have chosen "Continue replicating on data consistency errors" option for this Log Reader Agent Profile. Periodically, records are not getting inserted into Target. Most importantly there is no trace of errors logged into into distribution.dbo.MSrepl_errors.Any help in this regard is greatly appreciated.
Note: I have already informed our internal project team about bringing Source and Target servers on the same OS and SQL Version and they are working with our customer for their approval as it requires significant amount of downtime.

Thanks

Gladias

1 answer

Your answer

Answer 1

Anjan Kumar Chippa (Quadrant Resource LLC) 0 Microsoft External Staff Moderator

Hi Gladias Mariadoss,

Thank you for reaching out to SQL Q&A Forum.

Here the issue occurred because the subscription database was restored manually instead of using the supported Initialize from Backup or snapshot method. So this caused the LSN mismatch between publisher and subscriber, so some insert transactions got skipped without showing errors.

Please reinitialize the subscription properly either using with a snapshot or by following the documented Initialize from Backup process. This will align the LSNs and make sure all new inserts replicate correctly.

Please refer below document for your reference:

https://learn.microsoft.com/en-us/sql/relational-databases/replication/initialize-a-transactional-subscription-from-a-backup?view=sql-server-ver17

Thanks,
Anjan Kumar Chippa

Gladias Mariadoss 0 Reputation points

2025-11-07T14:53:09.9966667+00:00

Good day AnjanKumar, Thanks for taking your time to respond. This TR has been setup on October 10th. No issues for the first 14 days. The missing records have been found on October 24th. Nothing between 10th thru 24th. Secondly, the source DB size is about 800GB so initializing through TR snapshot method would take hours if not days to get the Target/Subscriber DB to be current. Not just that, we're given only 4-5hrs of downtime and it wouldn't be possible for Publisher/Distributor/Subscriber in sync. Means, data flow to Publisher (stopped during the setup) will start happening and catching up, we don't know when it'll get completed.

Thanks

Gladias
Anjan Kumar Chippa (Quadrant Resource LLC) 0 Reputation points Microsoft External Staff Moderator

2025-11-11T13:31:31.1533333+00:00

Hi Gladias Mariadoss,

Thank you for the update. Since replication worked fine for the first two weeks and the missing records appeared later, so looks like this indicates that the initial setup was valid but later the LSN chain between the publisher and subscriber became out of sync usually due to log truncation, cleanup or skipped transactions in the distribution database. That is why data stopped replicating even though no errors were reported.

For large databases where like a full snapshot is not feasible, the recommended approach is to reinitialize using the Initialize from Backup method. This allows you to take a recent backup of the published database and restore it to the subscriber during your 4-5 hour window and then mark it as initialized without running a full snapshot. This method minimizes the downtime and ensures the LSN’s are fully aligned again.

Thanks,
Anjan Kumar Chippa
Gladias Mariadoss 0 Reputation points

2025-11-12T22:10:30.96+00:00

Hi Anjan,

Appreciate your response back. Yes, I chose the later part i.e. backup/restore and then create the publication. Before the final step in SSMS Create Publication Wizard, there's a window wherein it shows 1) "Create a snapshot immediately and keep the snapshot available to initialize subscription" 2) "Schedule the Snapshot Agent to run at the following times". I usually uncheck these two options as they're not needed. Log reader agent profile has 2 options "1)Stop Synchronizing 2)Reinitialize Subscription". So, at what point you want me to do the "then mark it as initialized without running a full snapshot. This method minimizes the downtime and ensures the LSN’s are fully aligned again" ? And, do you want me to go thru this using T-SQL Commands ? I do have it but I prefer your examples.

Thanks

Gladias
Anjan Kumar Chippa (Quadrant Resource LLC) 0 Reputation points Microsoft External Staff Moderator

2025-12-02T10:34:19.6666667+00:00

Hi Gladias Mariadoss,

You don’t need any extra step here. After restoring the backup on the subscriber, you just simply create the subscription and choose Initialize with backup then the subscription is marked as initialized automatically. This is the supported way to keep the databases in sync without a full snapshot for large databases.

Thanks,
Anjan Kumar Chippa
Gladias Mariadoss 0 Reputation points

2025-12-03T15:15:30.6366667+00:00

Hi Anjan KC,

Thank you and with all the efforts have already been implemented, I still have the missing records time-to-time and I opened a case with MS Engr's. Had couple of meetings and sent logs and other info requested. Awaiting to hear back from them.

Thanks
Gladias

Share via

Transaction Replication - Missing data in Target

1 answer

Your answer