Drop table from a published SQL Server database

By:   |   Comments (1)   |   Related: > Replication


Sometimes there is a need to drop a SQL Server table from a database that is published for transactional replication. In this tip we will walk through the steps to do this and an easier way with SQL Server 2014 SP2 and later.


Suppose we have SQL Server transactional replication (the publication name is TestPublication) configured for the Adventurewors2014 database for two articles (i.e. tables) Company and Employee as shown below.

SQL Server Replication Articles

Now we have the requirement to drop the Company table from the database.  If we run the DROP command on the published database it fails with the below error message.

Drop table command fails for SQL Server Replicated tables

Since the table is being used in SQL Server transactional replication we cannot drop it since there are replication dependencies.

Drop article from published database prior to SQL Server 2014 SP2

To drop the table, first we need to remove it from the publication. We can use the sp_droparticle command. This stored procedure drops an article from a snapshot or transactional publication.

The syntax for sp_droparticle is:

    [ @publication= ] 'publication'  
      , [ @article= ] 'article'  
    [ , [ @ignore_distributor = ] ignore_distributor ]  
    [ , [ @force_invalidate_snapshot= ] force_invalidate_snapshot ]  
    [ , [ @publisher = ] 'publisher' ]  
    [ , [ @from_drop_publication = ] from_drop_publication ]  

We run the sp_droparticle command on the published database like so:

DECLARE @publication AS sysname;
DECLARE @article as sysname;
SET @publication=N'TestPublication';
SET @article=N'Company';

--Drop the transactional article.
Use [AdventureWorks2014]
EXEC sp_droparticle

sp_droparticle for SQL Server Transactional Replication

As we can see, we get an error that the article cannot be dropped from transactional replication since the subscriptions exist, so we have to first remove the subscriptions.

Drop subscriptions to a particular article

To drop the subscription for a particular database use the sp_dropsubscription stored procedure. This stored procedure is executed at the Publisher on the published database.

sp_dropsubscription [ [ @publication= ] 'publication' ]  
    [ , [ @article= ] 'article' ]  
      , [ @subscriber= ] 'subscriber'  
    [ , [ @destination_db= ] 'destination_db' ]  

So in my example, we need to drop the article from the TestReplication publication, for the Company article, for the subscriber Subscriber_Server for the destination database TestReplication.

EXEC sp_dropsubscription 'TestReplication','Company','Subscriber_Server','TestReplication'

sp_dropsubscription stored procedure

Drop article from publication

Now we need to drop the article using the sp_droparticle stored procedure command and we need to use the @force_invalidate_snapshot option.

DECLARE @publication AS sysname;
DECLARE @article as sysname;
SET @publication=N'TestPublication';
SET @article=N'Company';

--Drop the transactional article.
Use [AdventureWorks2014]
EXEC sp_droparticle

sp_droparticle stored procedure command with the @force_invalidate_snapshot option

If we don't use the @force_invalidate_snapshot=1. we get this error:

Msg 20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 101 [Batch Start Line 11] 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.

We can also do this step using SQL Server Management Studio by right clicking on the publication and selecting Properties.  On the Articles page if we uncheck the article, we get the below message:

SQL Server Management Studio Replication Publication Properties

Drop article from database

Now we can DROP the article from the published database using the DROP command.

Successful Drop Table command

This method needs to be followed for each article that needs to be dropped from the published database.

Drop article from published database using SQL Server 2014 SP2 or later

In SQL Server 2014 SP2 a change was added that allows a table thatís included as an article in a transactional replication publication to be dropped from the database and the publication.

To do so, we need to set the allow_drop property to TRUE on all the publications that have the article. If the property is set to FALSE for any of the publications that contain the article, then the DROP TABLE operation will fail and report that replicated articles canít be dropped. The default value of the allow_drop property is FALSE.

Syntax for making the change is as follows.

exec sp_changepublication 
 @publication = 'publication name', 
 @property = 'allow_drop', 
 @value = 'true'

So we will run it on the publication 'TestReplication' as follows:

exec sp_changepublication @publication='TestReplication', @property='allow_drop', @value='true'

Run the sp_changepublication stored procedure

Now we can drop the article using the DROP command.

Successful drop table in SQL Server Replication

Note: Please be careful while enabling this option as it allows database articles to be dropped directly from the published database, so it should be disabled once the task is completed.

To disable the allow_drop property we need to specify the value as FALSE as follows.

exec sp_changepublication @publication = 'TestReplication', @property = 'allow_drop', @value = 'false'
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, November 1, 2016 - 11:47:34 AM - Sreekanth Back To Top (43670)

@property = 'allow_drop' - Wow...Good to know! Thanks for the tip!

get free sql tips
agree to terms