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.
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