1

I have change data capture enabled for most of the tables in one of my databases. Database is hosted on SQL Server instance with version 2016.

I have some testing to be done, so I wanted to restore backup of that DB in my test environment. But I am really struggling with that (I am not sure what I am doing wrong).

Firstly I tried to restore that as any other DB. The restore is working fine... till the very end when is returning the error that it cannot drop CDC tables because it's not enabled. So I added keep_cdc keyword to restore command and it finished successfully. As I do not need CDC at test I wanted to disable it... but it's in a weird state:

exec sys.sp_cdc_disable_db returns error:

Could not update the metadata that indicates database DB is not enabled for Change Data Capture. The failure occurred when executing the command 'drop user cdc'. The error returned was 15284: 'The database principal has granted or denied permissions to objects in the database and cannot be dropped.'. Use the action and error to determine the cause of the failure and resubmit the request.

So... error says that CDC is disabled.. but when I run sys.sp_cdc_enable_db I got an error:

Database 'DB' is already 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.

And is_cdc_enabled indeed shows 1 for that DB. So I wanted to to use GUI. It also shows that cdc is enabled: [![enter image description here][1]][1] But, when I change that to 'False' after ok I am getting error:

Change tracking is enabled for one or more tables in database 'DB'. Disable change tracking on each table before disabling it for the database. Use the sys.change_tracking_tables catalog view to obtain a list of tables for which change tracking is enabled.

And now the tricky part that I do not understand at all. select * from sys.change_tracking_tables returns 30 tables. While sys.tables I had > 100 tables with is_tracked_by_cdc not equal to 0. I executed sys.sp_cdc_disable_table procedure for each such table select * from sys.tables where is_tracked_by_cdc <> 0 and as a result all these tables have is_tracked_by_cdc equal 0in sys.tables... but sys.change_tracking_tablesstill returns30tables and runningsys.sp_cdc_disable_table` against them does not change anything. As a result I cannot disable that feature on database level as well.

Could you please advise how can I get rid of CDC completely at such copy? (I was trying dropping all CDC object as well but without success)

1 Answer 1

0

Check which permissions that user has granted and remove them:

select * from sys.database_permissions
where grantor_principal_id = user_id ('cdc');

The error saya that cdc user ran grant or dey statement to another user (someone logged in with it, and ran some grant or deny statement)

After removing it, try disable cdc or try restore without keep_cdc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.