Cdc and how long are logs kept

Question

I started using SQL capture data change tables on Microsoft SQL Server 2016, it looks fairly easy to use mechanism, but now when I was using some tutorial I found and there was n info about that there is a limited time that data is kept in those tables, I think default is 3 days.

I was trying to find some info about it but with no luck so my questions stands:

Is there a way to increase that time that logs are kept or even turn it off.

Answer 1

You are looking for the Retention Period, which is indeed 3 days by default.

You can change it using sys.sp_cdc_change_job

USE [YourDatabase];

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

[ @retention ] =retention Number of minutes that change rows are to be retained in change tables. retention is bigint with a default of NULL, which indicates no change for this parameter. The maximum value is 52494800 (100 years). If specified, the value must be a positive integer. retention is valid only for cleanup jobs.

Please note, that this affects ALL tables marked to be tracked by CDC in the database, there is no way to configure it per table.

https://msdn.microsoft.com/en-us/library/bb510748(v=sql.105).aspx