I am using SQL 2012 sp3. I have CDC working on many tables and DBs without issue.
Last night a change was put into the system and 2 of my CDC'd tables were dropped and modified without properly removing CDC on those tables beforehand (it was done without my knowledge). I can no longer get CDC to work on those 2 tables.
I found the old capture instances for the tables and dropped them. When I add them back in, everything goes in error free, but CDC captures no data and
select sys.fn_cdc_get_min_lsn returns NULL.
I have tried adding/dropping the captures several times, adding data to the tables myself just to make sure some changes were going in, stop/starting SQLAgent and running the cleanup job. I also tried adding the captures back in under different a different name. Nothing works.
When I add both captures back in;
sys.sp_cdc_help_change_data_capture shows both of my captures.
select sys.fn_cdc_get_min_lsn('Capture_table1') returns null.
sys.dm_cdc_errors is empty.
select * from cdc.Capture_Table1_CT has no data in it (even though I made sure to add some data changes). My Agent and SQL logs are clean.
I read that running
sp_repldone can cleanup the log and may help CDC, however I hesitate to do that as this is running on an Availability Group and I am concerned it may have negative effects.
Is there anything else to try?