T-SQL Change Data Capture log cleanup

Question

I have enabled CDC on few tables in my SQL server 2008 database. I want to change the number of days I can keep the change history.

I have read that by default change logs are kept for 3 days, before they are deleted by sys.sp_cdc_cleanup_change_table stored proc.

Does anyone know how I can change this default value, so that I can keep the logs for longer.

Thanks

Answer 1

You need to update the cdc_jobs.retention field for your database. The record in the cdc_jobs table won't exist until at least one table has been enabled for CDC.

-- modify msdb.dbo.cdc_jobs.retention value (in minutes) to be the length of time to keep change-tracked data
update
    j
set
    [retention] = 3679200 -- 7 years
from
    sys.databases d
inner join
    msdb.dbo.cdc_jobs j
        on j.database_id = d.database_id
        and j.job_type = 'cleanup'
        and d.name = '<Database Name, sysname, DatabaseName>';

Replace <Database Name, sysname, DatabaseName> with your database name.