Change Data Capture is not Deleting Rows Older than the Configured Retention Period

Question

I enabled change data capture and set the retention to 2 days. I have CU update 31 and above installed. Old CDC records are not deleting.

The SQL Agent cleanup jobs are running successfully.

What should I look at to resolve this? Is there anything else I should be looking at to resolve this?

My DBAs would not be happy if I deploy this to prod in it current state.

I analyzed the code of the SQL agent jobs. It does appear as some stored procedures are missing. However, the jobs run successfully without error.

Answer 1

I had "Change Tracking" settings confused with "Change Data Capture" settings. Running this solves retention and purging of old records for CDC.

EXECUTE sys.sp_cdc_change_job
    @job_type = N'cleanup',
    @retention = 15;