MSSQL transaction replication – How to resync the data?

All we need is an easy explanation of the problem, so here it is.

I have 1 transaction replication (peer to peer topology). After I setup, I found that the data on 2 table was difference.

The first peer table [VPMYN204].[AdventureWorks2019].[Person].[AddressType] have 6 rows.
And the second table [AdventureWorks2019].[Person].[AddressType] have 7 rows.

MSSQL transaction replication - How to resync the data?

  1. What should I do?
    (P/S: I am new to transaction replication feature.)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I’ve seen certain operations throw Transactional Replication out of whack before, and the first turn it off, turn it on again solution I’ll go to when that happens is reinitializing the subscription for that Publication. Per the aforementioned documentation:

Connect to the Publisher in Management Studio, and then expand the server node.

Expand the Replication folder, and then expand the Local Publications folder.

Expand the publication that has the subscription you want to reinitialize.

Right-click the subscription, and then click Reinitialize.

In the Reinitialize Subscription(s) dialog box, select options, and then click Mark for Reinitialization.

After that I’ll usually monitor the snapshot agent status since the above generates a new snapshot that your Transactional Replication Publication will work off of, and I like to ensure it finishes replicating correctly.

After that’s all done, you should be in sync now from the new snapshot, but you should try a data test, as I’ve seen worse cases where this didn’t fix the issue. If you’re still hosed at this point, you may have to drop and recreate your Transactional Replication Publisher and Subscriber(s), unfortunately.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply