CDC not working

• sksingh123

  SSC Veteran

  Points: 265

  More actions

  May 4, 2016 at 2:06 am

  #316273

  Hi ,

  We are facing challenges with CDC on SQL SERVER 2012 Enterprise Edition , it was seen that CDC stopped working few days back (chande data re not being populated in CT tables however I can see the latest LSN captured in lsn_time_mapping ) and upon checking sys.dm_cdc_errors we didnt found any errors .

  Also we have seen that CDC capture job is running fine . But is it not able to write data in the CDC tables . When we ran the profiler ,we found below mentioned error messages :

  The log scan number (1648:119995:1) passed to log scan in database 'xxxx' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

  With reference to blog(http://i1.blogs.msdn.com/b/repltalk/archive/2014/07/22/sql-server-profiler-showing-9003-exception-when-cdc-is-configured.aspx) it has bene mentioned that these messages can be ignored. Could you please let us know if this error message could be the problem in this case.

  Message

  Change Data Capture has scanned the log from LSN{00000670:0001E0A6:0003} to LSN{00000670:0001E0A6:0007}, 1 transactions with 1 commands have been extracted. To report on the progress of the operation, query the sys.dm_cdc_log_scan_sessions dynamic management view.

  Also we tried stop and start CDC capture job and it has initated and it seems like it is reading transactions ( giving the information like

  ) but the data is still not visible in the CDC tables.

  scan_phase column in last row of select * from sys.dm_cdc_log_scan_sessions showing as "Aggregate" and it's stuck at this however other rows shows as "Done"

  no error in select * from sys.dm_cdc_errors;

  The database size is approx . 10 GB.

  Any pointers will be much appreciated !