Take the 2-minute tour ×

I dropped a table before disabling CDC for that. Now when I recreated the table and tried enabling CDC it says that capture instance already exists. I can use a different Capture Instance name but need to know if there is anyway to drop the associated capture instance manually.

When I delete a table through SSMS GUI it drops CDC tables too. But this time I dropped the table using code and it didn't disable or remove CDC. Hence the trouble. Ms documentation talks about a hot fix if Change Table are removed by mistake. But I have removed the base table. Any clues on how to remove this capture instance for the dropped table?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

Well I figured out a way. I removed all the records related to that table from all CDC system tables and tried recreating the capture instance with same name. It worked!

share|improve this answer
Have you got a code example, when you said all the system tables? What ones? Having the same issue. Thanks! –  user779248 Jun 1 '11 at 11:09
Sorry, it has been a while now.. dont remember. But it was not hard... Just spend some time on it and you will figure out... –  Faiz Jun 3 '11 at 20:31
add comment

Here are the steps I took to remove an orphaned capture instance in CDC:

DROP FUNCTION [cdc].[fn_cdc_get_net_changes_dbo_(tablename)]
DROP FUNCTION [cdc].[fn_cdc_get_all_changes_dbo_(tablename)]

Then run the following:

declare @objid int
set @objid = (select object_id from cdc.change_tables where capture_instance = 'your orphaned capture instance')

delete from cdc.index_columns where object_id = @objid
delete from cdc.captured_columns where object_id = @objid
delete from cdc.change_tables where object_id = @objid

At that point you should be able to re-create your capture instance via sp_cdc_enable_table as normal.

share|improve this answer
add comment

I had to execute one more step in addition to the REPLY by pdanke:

DROP TABLE cdc.<capture_insance>_CT

My cdc orphan may have come about when I restored a database where change data capture had been enabled. In my case,

EXECUTE sys.sp_cdc_help_change_data_capture 

resulted in one entry where source_schema and source_table were both NULL.

share|improve this answer
add comment

Your Answer


