question

AlekseyP13-8878 avatar image
0 Votes"
AlekseyP13-8878 asked AlekseyP13-8878 commented

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

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered AlekseyP13-8878 commented

Hi @AlekseyP13-8878,

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

As I understand, this job wasn't created because cdc data used for our internal functionality and it shouldn't be deleted. And only now, when our team developed appropriate solution to isolate our internal functionality from cdc data, finally we have no need to store all this cdc data.

How I found that this job deletes some cdc data? - I counted amount of data in cdc tables before and after cdc cleanup job run.

0 Votes 0 ·