Change data capture (CDC), cleanup job removes some records while computed low_water_mark_time is less than oldest lsn_time_mapping -> tran_end_time

Question

I've to test a new development among which is creation and configuration of cdc cleanup job (via sys.sp_cdc_add_job).
cleanup job -> retention set to value which makes it able to delete retained cdc data only after 6 months (max(tran_end_time) - retention < min(tran_end_time)).
BUT, I've run this job with this retention value and it anyway deletes some cdc data. Why is this happened? Is there some additional logic in this cleanup job (i.e. to delete cdc data for which doesn't exist linked real data)?

Answer 1

Hi @AlekseyP13 ,

Why did you create cleanup job manually? Did you delete the default clean up job? Quote from MS document sys.sp_cdc_add_job (Transact-SQL);

>Because the cleanup and capture jobs are created by default, this stored procedure is necessary only when a job has been explicitly dropped and must be recreated.

How did you find this job deletes some cdc data during retention value?

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".