FIX: Can't enable or disable change data capture for a database after you attach it in SQL Server 2014, 2016, or 2017

Gjelder: SQL Server 2014 StandardSQL Server 2014 DeveloperSQL Server 2014 Enterprise Core

Symptoms


Assume that you attach a database that's enabled for change data capture in Microsoft SQL Server 2014, 2016, or 2017. If there are transactions to be redone during the startup of the database, change data capture may run into an inconsistent state, that is, change data capture is in disabled state, but the change data capture objects still exist. Additionally, you may encounter the following situations:

  • When you try to disable the change data capture for this database by using the sp_cdc_disable_db stored procedure, you receive the following error message:

    The database DatabaseName is not enabled for Change Data Capture. Ensure that the correct database context is set and retry the operation. To report on the databases enabled for Change Data Capture, query the is_cdc_enabled column in the sys.databases catalog view.
  • When you try to enable the change data capture for this database by using the sp_cdc_enable_db stored procedure, you receive the following error message:

    Msg 22906, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line LineNumber

    The database DatabaseName cannot be enabled for Change Data Capture because a database user named UserName or a schema named SchemaName already exists in the current database. These objects are required exclusively by Change Data Capture. Drop or rename the user or schema and retry the operation.

Resolution


This issue is fixed in the following cumulative updates for SQL Server:

       Cumulative update 8 for SQL Server 2016 SP1  

       Cumulative Update 4 for SQL Server 2017

       Cumulative Update 9 for SQL Server 2014 SP2

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References


Learn about the terminology that Microsoft uses to describe software updates.