Post reply

CDC table fails to either enable or disable

  • March 22, 2017 at 8:37 am

    #401826

    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.

  • September 19, 2023 at 2:03 am

    #4289228

    Sorry for the 6 year delay in response but this happened to me today.  Here is the solution:

    The problem is that something causes 1 table and 2 functions to be left behind.  All 3 are in the cdc schema and have names that include your base table.  This query should reveal them to you.

    SELECT type_desc, name 
    FROM sys.objects 
    WHERE name LIKE '%MyTable%' AND SCHEMA_NAME(schema_id) = 'cdc'

    Drop the table and 2 functions.  You might also need to delete the records from the cdc.captured_columns table where the column_name in (the column names from your capture instance).

    This worked for me.  I was then able to create the capture instance.

    • This reply was modified 1 year, 2 months ago by  Sam Jumper.
    • This reply was modified 1 year, 2 months ago by  Sam Jumper.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply