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 1 month, 3 weeks ago by jaya.