2

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?

  • does SELECT * FROM cdc.change_tables show the correct object_id values for the tables in question? – Max Vernon Sep 22 '16 at 19:01
  • It does have the correct object_id values. – Gdog Sep 22 '16 at 19:48
  • 1
    I fixed my issue. It was a much larger problem. I started troubleshooting with a very narrow scope -- just the 2 tables that were effected by the change. It turns out that all of my CDC for the entire server was down. Upon further investigation I realized that it was the AG that was causing this. We had a power outage days before and one of my replicas was still down. As a result the log was not hardened and CDC stops recording changes. Once I pulled my 3rd replica off of the AG all of CDC started working. – Gdog Sep 23 '16 at 16:39
  • If you care to share some details about the steps you took, you could answer your own question, which may help someone in future. If you do that, you may want to edit your question if necessary. – Max Vernon Sep 23 '16 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.