Thursday, October 13, 2011

Remove an Article from Transactional Replication without dropping the Subscription

          While using Transactional Replication, there may come a time when you need to drop an article from a publication, a publication that may have one or more subscriptions. You would think that the process would be very similar to adding an article to the publication; open up the article and uncheck the box. Now, if there were no subscribers to the publication, this would not be an issue, but odds are that this publication has been used for some time and there are one or more subscribers to it. If this is the case, when you go to uncheck the article to remove it, you may receive an message stating that since there are subscriptions on this table, you will have to reinitialize this subscription and generate a new snapshot.

          So how can we get around re-initialization of the subscriber and new snapshot generation? We manually execute some of the replication stored procedures to remove the article and keep the snapshot from being invalidated.

First we must use
sp_dropsubscription to remove the subscription to the individual article.

EXEC sys.sp_dropsubscription
    @publication = '<PublicationName>',
    @article = '<ArticleToDrop>',
    @subscriber = '<SubscribingServer>',
    @destination_db = '<DestinationDatabase>'
Next, we drop the article from the publication without invalidating the snapshot. We do that by executing sp_droparticle with the force_invalidate_snapshot set to 0.

EXEC sys.sp_droparticle @publication = '<PublicationName>',
    @article = '<ArticleToDrop>',
    @force_invalidate_snapshot = 0


  1. Msg 20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 99
    Cannot make the change because a snapshot is already generated. Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.