Dropping Transactional Articles

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.

21 thoughts on “Dropping Transactional Articles”

  1. 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!

    1. 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

      1. 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.

  2. Can you please write an article about troubleshooting and minimizing downtime if the distribution database server goes down completely in Transaction Replication.

  3. 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

    1. 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

      1. 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

        1. 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.

Leave a Reply

Your email address will not be published.