Supports DROP TABLE DDL for articles that are included in transactional replication in SQL Server 2014 or in SQL Server 2016 SP1

This article describes an improvement that allows a table that's included as an article in transactional replication publication to be dropped from the database and the publication(s). A table can be dropped only if the allow_drop property is set to TRUE on all the publications that have the table(s) as an 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.

If a DROP TABLE DDL is supported by the publication(s), then the DROP TABLE operation will drop the table from the publication and the database. The log reader agent will post a cleanup command for the distribution database of the dropped table and do the cleanup of the publisher metadata. If the log reader hasn't processed all the log records that refer to the dropped table, then it will ignore new commands that are associated with the dropped table. Already processed records will be delivered to distribution database. They may be applied on Subscriber database if the Distribution Agent processes them before Log Reader cleans up the obsolete (dropped) article(s).

Command to enable drop table support for a publication:
exec sp_changepublication @publication = '<Publication Name>', @property = 'allow_drop', @value = 'true'
Command to enable drop table support while you're creating a publication:

exec sp_addpublication @publication = '<Publication Name>', ..., @allow_drop = N'true'
The default value of the allow_drop property is set to False.
Resolution
This problem was fixed in the following cumulative updates for SQL Server:
About cumulative updates for SQL Server
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:

Service pack information

This functionality was first introduced in the following service packs for SQL Server:

About Service packs for SQL Server

Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. Our recommendation is to apply the latest service pack and the latest cumulative update for that service pack. You don't have to install a previous service pack before you install the latest service pack. Use Table 1 in the following article for finding more information about the latest service pack and latest cumulative update.

How to determine the version, edition and update level of SQL Server and its components
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Properties

Article ID: 3170123 - Last Review: 11/16/2016 10:32:00 - Revision: 3.0

Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise Core, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Web, Microsoft SQL Server 2016 Developer, Microsoft SQL Server 2016 Enterprise, Microsoft SQL Server 2016 Enterprise Core, Microsoft SQL Server 2016 Standard

  • kbqfe kbfix kbsurveynew kbexpertiseadvanced KB3170123
Feedback