So I think I have a good one.
I am seeing a very peculiar issue with CDC right now. For some background :
- The one change we made to this table was we added a unique index to it.
- We recently upgraded to SQL2014 SP2
- This would have been the second deployment on the upgrade
- I didn't notice any failures during the initial disable
This process usually runs without incident until after this deployment, one of the tables gave this error after running - sys.sp_cdc_enable_table:
Could not update the metadata that indicates table [dbo].[MyTable] is enabled for Change Data Capture. The failure occurred when executing the command 'sys.sp_getapplock @Resource = @instance_resource, @LockMode = N'Exclusive', @LockOwner = 'Transaction', @DbPrincipal = 'cdc' '. The error returned was 22926: 'Could not create a capture instance because the capture instance name 'dbo_MyTable' already exists in the current database. Specify an explicit unique name for the parameter @capture_instance.'. Use the action and error to determine the cause of the failure and resubmit the request.
So, one would think, it was already enabled. I checked the following:
- is_tracked_by_cdc is 0 in sys.tables
- The table doesn't exist in cdc.change_tables
- The proc sys.sp_cdc_help_change_data_capture doesn't show the table in question.
- The only place I could find any semblance of metadata was in the table cdc.captured_columns which had all the columns for the table?? I tried manually deleting those rows from this table (I was desperate), same errors.
I've also tried "re-disabling" the table from cdc and get this:
Msg 22960, Level 16, State 1, Procedure sp_cdc_disable_table_internal, Line 75
Change data capture instance 'dbo_MyTable' has not been enabled for the source table 'dbo.MyTable'. Use sys.sp_cdc_help_change_data_capture to verify the capture instance name and retry the operation.
However, as stated above that proc does not show that cdc table. Also, we did this same exact process and change to 13 other segments and had no issues as well as have been doing this process for years. I'm thinking it may be some weird bug or perhaps there is some metadata table I'm not thinking of right now.
Any help is appreciated.