In MSDN in the article “Add Articles to and Drop Articles from Existing Publications” there is a section which says “For snapshot or transactional publications, articles can be dropped with no special considerations prior to subscriptions being created. If an article is dropped after one or more subscriptions is created, the subscriptions must be dropped, recreated, and synchronized.”. This is pretty scary and sounds almost like a reinitialization is needed!
Let’s see what happens when we use SSMS. If you drop an article from the publication you’ll see the following dialogue:
“This will not impact existing subscriptions” sounds reassuring, but is slightly ambiguous – perhaps it is only referring to the invalidation of the snapshot. However there is clearly no mention that “the subscriptions must be dropped, recreated, and synchronized”. If you click on “Yes” you’ll find that the next time you start the distribution agent all runs fine. There will be some orphaned objects left on the subscriber – stored procedures related to the article and the like – but nothing to worry about. So perhaps the MSDN article is incorrect?
Not really! The SSMS interface is clever enough to do the hard work for you. If you try to drop the article yourself using this type of script:
EXEC sp_droparticle
@publication = ‘pubtestarticle’,
@article = ‘tcity3’,
@force_invalidate_snapshot = 1;
GO
You’ll receive an error:
Msg 14046, Level 16, State 1, Procedure sp_MSrepl_droparticle, Line 286 [Batch Start Line 0]
Could not drop article. A subscription exists on it.
What you need to do is to manually drop the subscriptions it has and then you can drop the article.
EXEC sp_dropsubscription ‘pubtestarticle’,’tcity3′,’desktop-nsonkmo’,’transub’
This is what SSMS does behind the scenes and this is all the MSDN article is really telling us – there is no need to reinitialize, just drop the relevant subscriptions and then the article and then synchronize.
Since the table is being used in SQL Server transactional replication we cannot drop it since there are replication dependencies.
Let me update this with all the cases in which dropping won’t work….
I love the fact that Paul is back to blogging again. But why have you removed all the old ones? All of them were gems for replication troubleshooting. Hoping that you would add it back. Keep up doing the great work!
Hi Nainesh – thanks for this – I’m slowly rewriting any articles I think are still relevant. Did you have a ny particular ones in mins and I’ll prioritise those. Cheers, Paul
I would love to see articles on below:
1) Replication optimization in terms of speed of delivery of snapshot.
2) Replication agent profile parameters to use for snapshot, log reader and distributor agent.
OK – will do.
Hi Nainesh – this post answers the snapshot optimisation bit: http://replicationanswers.com/wp-admin/post.php?post=130&action=edit
Cheers,
Paul
That’s to the admin interface. I assume it is some time around Sept-3?
Hi – I’m not too sure what the question is? The article was published on Feb 7th if that helps?
Thanks.
Well explained Paul.
Thanks for the article and making thinks clear.
Thanks 🙂
Can you please write an article about troubleshooting and minimizing downtime if the distribution database server goes down completely in Transaction Replication.
OK – will do.
Please also provide link to older blogs will be highly helpful sir
Hi Manish – please can you tell me which articles you need access to and I’ll upgrade to WordPress format.
Here I have 1 doubt pls
When we drop subscription, how distribution agent knows no need to apply new snapshot or there are 2 snapshots generated 1) for full dB snapshot without dropped table 2 ) drop table script
Pls elaborate will be helpful
Hi Sangeeta.
There is only one type of snapshot which is valid once the table has been dropped. The old snapshot which contains the article is invalid. The new snapshot is only created if you have a new subscriber or want/need to reinitialize. Other than that there is no need for it – much the same as when a DDL statement is issued.
Cheers,
Paul
Please provide link to old articles. Please
Hi – please tell me which articles you need access to and I’ll upgrade them to WordPress format.
Please & please……………………. provide old links
Hi – please tell me which articles you need access to and I’ll upgrade them to WordPress format.
Admin Sir, Please once you make available all the old notes, we will go through all these SUPERB articles and then we will ask.sir, please do the needful or publish 1 ebook with link.we will download.
Please these were the notes always I referred to in last 6-7 years and not finding them now when we still have sql 2000 version is frightening and giving me nitemare.
I hope you understand our problem sir
Sorry Manishkumar – all articles are now for SQL 2016/2017. Most of the old asp pages have been deleted. Let me know if you have a specific question about SQL 2000 and I’ll try to help.