Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Modifies the configuration of a change data capture cleanup or capture job in the current database. To view the current configuration of a job, query the dbo.cdc_jobs table, or use sp_cdc_help_jobs.
Type of job to modify. job_type is nvarchar(20) with a default of 'capture'. Valid inputs are 'capture' and 'cleanup'.
[ @maxtrans ] =max_trans
Maximum number of transactions to process in each scan cycle. max_trans is int with a default of NULL, which indicates no change for this parameter. If specified, the value must be a positive integer.
max_trans is valid only for capture jobs.
[ @maxscans ] =max_scans
Maximum number of scan cycles to execute in order to extract all rows from the log. max_scans is int with a default of NULL, which indicates no change for this parameter.
max_scan is valid only for capture jobs.
[ @continuous ] =continuous
Indicates whether the capture job is to run continuously (1), or run only once (0). continuous is bit with a default of NULL, which indicates no change for this parameter.
When continuous = 1, the sp_cdc_scan job scans the log and processes up to (max_trans * max_scans) transactions. It then waits the number of seconds specified in polling_interval before beginning the next log scan.
When continuous = 0, the sp_cdc_scan job executes up to max_scans scans of the log, processing up to max_trans transactions during each scan, and then exits.
If @continuous is changed from 1 to 0, @pollinginterval is automatically set to 0. A value specified for @pollinginterval other than 0 is ignored.
If @continuous is omitted or explicitly set to NULL and @pollinginterval is explicitly set to a value greater than 0, @continuous is automatically set to 1.
continuous is valid only for capture jobs.
[ @pollinginterval ] =polling_interval
Number of seconds between log scan cycles. polling_interval is bigint with a default of NULL, which indicates no change for this parameter.
polling_interval is valid only for capture jobs when continuous is set to 1.
[ @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.
[ @threshold= ] 'delete threshold'
Maximum number of delete entries that can be deleted using a single statement on cleanup. delete threshold is bigint with a default of NULL, which indicates no change for this parameter. delete threshold is valid only for cleanup jobs.
The following example updates the @job_type, @maxscans, and @maxtrans parameters of a capture job in the AdventureWorks2012 database. The other valid parameters for a capture job, @continuous and @pollinginterval, are omitted; their values are not modified.