Configure Change Data Capture (CDC) Parameters in SQL Server

After enabling CDC in SQL Server, We can modify the retention period and the number of transactions that to be handled in Change Data Capture table.

Before configureing one should understand the basic terms in CDC Configuration:

Polling interval – (Default 5 seconds) the amount of time to wait between log scans

Maxtrans – (Default 500) the number of transactions to grab with each scan

Maxscans – (Default 10) the number of scans performed between each polling interval

Retention – (Default 72 hours, 4320 mins, 3 days). 

The period for which the new/updated/deleted data have to be retrieved and displayed in CDC tables. 

Execute the below query to get the CDC configured values:

SELECT * from msdb.dbo.cdc_jobs

Execute the below query to change capture instances:

EXEC sys.sp_cdc_change_job @job_type = 'capture'

       ,@maxtrans = 501

       ,@maxscans = 10

       ,@continuous = 1

       ,@pollinginterval = 5

Execute the below query to change retention period:

EXEC sys.sp_cdc_change_job @job_type = 'cleanup'

                          ,@retention = 4320 -- Number of minutes to retain (72 hours)

                          ,@threshold = 5000

Using this method we can use CDC hold the required period of historical data, i.e., for last 1 month, last 1 year or last 10 days, etc.