CDC fails after attaching database to SQL Server 2008 R2 instance

Question

We have a SQL Server database hosted on SQL Server 2008 R2 (SP 1) with CDC (change data capture) enabled.

After a re-install of the SQL Server instance due to license expiration (the deployment team forgot to install the correct license :( ) we attached the database to the newly installed instance.

However, the CDC was not enabled for all tables When we tried to enable it by executing

sys.sp_cdc_enable_table

we got

The database 'DBName' 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.

Suggesting that CDC is not enabled.

So, we tried to enable it by executing

sys.sp_cdc_enable_db

we got the following error:

Msg 22906, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 49
The database 'DBName' cannot be enabled for Change Data Capture because a database user named 'cdc' or a schema named 'cdc' 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.

After trying to disable the CDC by executing

sys.sp_cdc_disable_db

we got the error the same error again:

The database 'DBName' 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.

My guess is that there is some inconsistency between the db system tables and the SQL server system tables that lead to an invalid state of the CDC.

Is there any way to fix that?

Any thoughts are appreciated.

Answer 1

Hi For repairing Db ( with dropping everything in the cdc schema and the schema itself ) use this :

DECLARE @tableName NVARCHAR(100);
DECLARE myCursor CURSOR FORWARD_ONLY FAST_FORWARD READ_ONLY
FOR
    SELECT  QUOTENAME(t.name) AS name
    FROM    sys.tables t
            JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE   s.name = 'cdc'
OPEN myCursor 
FETCH FROM myCursor INTO @TableName 
WHILE ( @@Fetch_Status = 0 ) 
    BEGIN 

        EXEC ( 'drop table cdc.' + @TableName + '; ' );
        FETCH NEXT FROM myCursor INTO @TableName 
    END  
CLOSE myCursor 
DEALLOCATE myCursor;
go

DECLARE @prName NVARCHAR(100);
DECLARE myCursor2 CURSOR FORWARD_ONLY FAST_FORWARD READ_ONLY
FOR
    SELECT  QUOTENAME(pr.name) AS name
    FROM    sys.procedures pr
            JOIN sys.schemas s ON pr.schema_id = s.schema_id
    WHERE   s.name = 'cdc'
OPEN myCursor2 
FETCH FROM myCursor2 INTO @prName 
WHILE ( @@Fetch_Status = 0 ) 
    BEGIN 
        EXEC ( 'drop procedure cdc.' + @prName + '; ' );
        FETCH NEXT FROM myCursor2 INTO @prName 
    END  
CLOSE myCursor2
DEALLOCATE myCursor2 

GO

DECLARE @fnName NVARCHAR(100);
DECLARE myCursor3 CURSOR FORWARD_ONLY FAST_FORWARD READ_ONLY
FOR
    SELECT  QUOTENAME(fn.name) AS name
    FROM    sys.objects fn
            JOIN sys.schemas s ON fn.schema_id = s.schema_id
    WHERE   fn.type IN ( 'FN', 'IF', 'TF' )
            AND s.name = 'cdc'
OPEN myCursor3 
FETCH FROM myCursor3 INTO @fnName 
WHILE ( @@Fetch_Status = 0 ) 
    BEGIN 
        EXEC ( 'drop function cdc.' + @fnName + '; ' );
        FETCH NEXT FROM myCursor3 INTO @fnName 
    END  
CLOSE myCursor3
DEALLOCATE myCursor3 
go
DECLARE @ruleName NVARCHAR(100);
SELECT  @ruleName = DP1.name
FROM    sys.database_principals AS DP1
        JOIN sys.database_principals AS DP2 ON DP1.owning_principal_id = DP2.principal_id
WHERE   DP1.type = 'R'
        AND DP2.name = 'cdc';
EXEC ('ALTER AUTHORIZATION ON ROLE::'+@ruleName+' TO dbo; ')
go 
DROP SCHEMA [cdc]
GO
DROP USER [cdc]
GO

Answer 2

I've also run into the bad ways in which CDC en/disabling is handled. If I were you, I'd try dropping everything in the cdc schema and the schema itself and try enabling CDC in the database again. Alternatively, if you're restoring (as opposed to attaching), there's a KEEP_CDC option.