CDC - I cannot disable that on the second instance
1
vote
1
answer
1025
views
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]]
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 0
in sys.tables
... but sys.change_tracking_tables
still returns 30
tables and running sys.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)
Asked by Radek Gąska
(192 rep)
Jul 25, 2022, 01:57 PM
Last activity: Feb 16, 2025, 05:01 AM
Last activity: Feb 16, 2025, 05:01 AM