change data capture errors cdc_errors

• jaya

  SSCrazy

  Points: 2109

  More actions

  April 7, 2020 at 3:50 pm

  #3741088

  The cdc capture job ran into errors on our prod server. The error reporting in dm_cdc_errors is

  Cannot insert duplicate key row in object 'cdc.CI_dbo_valuation_CT'

  WITH unique index 'CI_dbo_valuation_t_CT_clustered_idx'.

  The duplicate key value is (0x005e624e00063c60002c, 1, 0x005e624e00063740005c, 2).

  The index definition is on the following columns. The following selects shows that there is already a row that was captured.

  SELECT * FROM cdc.CI_dbo_valuation_CT

  WHERE [__$start_lsn] = 0x005e624e00063c60002c

  AND [__$command_id] = 1

  AND [__$seqval] = 0x005e624e00063740005c

  AND [__$operation] = 2

   

  My google search showed that there is a bug with sql2016 when Merge statement used with cdc, capture job fails. This was rectified in CU1. https://support.microsoft.com/en-us/help/3155503/fix-merge-statement-to-sync-tables-is-unsuccessful-when-change-data-ca

  But we are running sql2016 CU11 and we are seeing this error. Restarting the CDC capture job fixed the error and capture job was able to move past the LSNs. I am trying to find out the root cause of this problem.

  Any help would be great.

  • This topic was modified 5 years, 3 months ago by jaya.

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  April 8, 2020 at 4:10 pm

  #3741484

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.