Configure Change Data Capture Settings

After setting up change data capture (CDC), there are a few configuration options that allow for optimal performance based upon applications load.

Using the default values, the most number of transactions that can be grabbed every 5 seconds is 5000. Remember, a transaction is not the same as a database row. One transaction can update “n” number of rows.

In order to determine latency for you CDC solution, you will need to query a DMV view:

[cc lang=”sql”]
select latency, *
from sys.dm_cdc_log_scan_sessions
[/cc]

The latency will be shown in seconds, if it is getting too high, start increasing maxtrans, maxscans, and decrease the polling interval.

  • 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

In order to change these settings, use the system stored procedure (sys.sp_cdc_change_job) after changing the database which contains your capture instance. The CDC job needs to be restarted after making these changes.

[cc lang=”sql”]
— Change to the database that contains your capture instance
use YourDatabaseHere
go
exec sys.sp_cdc_change_job @job_type = ‘capture’
,@maxtrans = 501
,@maxscans = 10
,@continuous = 1
,@pollinginterval = 5
[/cc]

Changing the latency between the cleanup is done using the same stored procedure:

[cc lang=”sql”]
exec sys.sp_cdc_change_job @job_type = ‘cleanup’
,@retention = 4320 — Number of minutes to retain (72 hours)
,@threshold = 5000
[/cc]

To view the results of the changes, execute the following:
[cc lang=”sql”]
— View the results of the changed parameter is the cdc_jobs table
SELECT *
FROM msdb.dbo.cdc_jobs
[/cc]

2 comments
Ray Ayyelos 08 Jul 2014 at 10:26 pm

Thanks for the tip on increasing max trans and scans if/when latency gets too high. I was having an issue where CDC was incurring [log_reuse_wait_desc] as ‘REPLICATION’ and consuming a lot of log space and keeping it near capacity. Altering the configuration temporarily (perhaps permanently) substantially enough allowed for that backlog to clear and reduce my log use to less alarming levels. Again, thanks!

MUJEEB 05 Sep 2013 at 4:18 pm

Nice query on your web site

Featured Articles

 Site Author

  • Thanks for visiting!
css.php