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

AlekseyP13 1 Reputation point
2022-05-26T12:45:03.523+00:00

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)?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,757 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,554 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2022-05-27T06:39:59.437+00:00

    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".