sql server – NoSync subscriptions will need to be dropped and recreated

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

I am having this error message from the sql server replication monitor:

Error messages: The subscription(s) have been marked inactive and must
be reinitialized. NoSync subscriptions will need to be dropped and
recreated. (Source: MSSQLServer, Error number: 21074) Get help:

enter image description here

The publication is SupplierDB.

I have just generated an snapshot, as you can see on the picture below.

enter image description here

I have followed the answers and comments and not sure what else,
my subscription is now working, however, it shows up twice now, and I can’t find a way to get rid of the one that is not working.

enter image description here

I got it double for some reason.
enter image description here

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

You can update the registry of the Subcriber

here you will see the actual status

select * from [Publish_Database]..syssubscriptions

here the update for all articles subscribes

update [Publish_Database]..syssubscriptions set status=2 where srvname='server'

After that the snapshot should become effective again

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