CDC Retention Period

With reference to my earlier article on restoring CDC, here is one more catch!

By default retention period is 3 days (4320 minutes). So when we restore CDC backup and then enable the job, the job tries to clean up all data from the restore which is older than 3 days. Hence we have to make sure the retention period is passed as a parameter while we re-enable the job.

Here is the piece of code which needs to be updated
EXECUTE sys.sp_cdc_add_job
@job_type = N'cleanup',
@retention = 43200; --for 30 days
GO

You can also change an exisitng CDC job using the below code
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = 21600; --for 15days
GO

This information is stored in MSDB.sys.cdc_jobs