1

Today I re-enabled CDC on the primary node of an availability group. When the jobs were created the cleanup job failed with the following error:

Could not delete change table entries made obsolete by a change in one or more low water marks for capture instances of database DB1. The failure occurred when executing the command 'delete top( @p1 ) from [cdc].[dbo_Table1_CT] where __$start_lsn < @p2 '. The error returned was 208: 'Invalid object name 'cdc.dbo_Table1_CT'.'. Use the action and error to determine the cause of the failure and resubmit the request. [SQLSTATE 01000] (Message 22852).

Using sys.sp_cdc_help_change_data_capture I can see for the invalid object name that there is an entry for the capture_instance, but the source_schema and source_table are NULL. Both enabled and disabling the table fails with the following errors:

Disable:

Change data capture instance 'dbo_Table1' has not been enabled for the source table 'dbo.Table1'.

Enable:

Could not create a capture instance because the capture instance name 'dbo_Table1' already exists in the current database.

I'm at a loss on how to get this either removed from CDC or updated so the NULLs don't appear.

1 Answer 1

0

It looks like the CDC table has been orphaned.

You can try connecting using the DAC connection and dropping that table.

Easiest is to connect using sqlcmd

sqlcmd.exe -A -D YourDb

Then just drop the history table

DROP TABLE [cdc].[dbo_Table1_CT];
GO

You may also want to drop the functions if they exist

DROP FUNCTION cdc.fn_cdc_get_all_changes_dbo_Table1_CT;
GO
DROP FUNCTION cdc.fn_cdc_get_net_changes_dbo_Table1_CT;
GO

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.