Configure Change Data Capture Parameters in SQL Server

After enabling CDC in SQL Server (see: http://mahadevanrv.blogspot.in/2011/05/change-data-capture-in-sql-server.html). 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.