Applies to: SQL Server (all supported versions)
sys.sp_cdc_change_job [ [ @job_type = ] 'job_type' ] [ , [ @maxtrans = ] max_trans ] [ , [ @maxscans = ] max_scans ] [ , [ @continuous = ] continuous ] [ , [ @pollinginterval = ] polling_interval ] [ , [ @retention ] = retention ] [ @threshold = ] 'delete threshold'
[ @job_type = ] 'job_type'
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.
Return Code Values
0 (success) or 1 (failure)
If a parameter is omitted, the associated value in the dbo.cdc_jobs table is not updated. A parameter set explicitly to NULL is treated as though the parameter is omitted.
Specifying a parameter that is invalid for the job type will cause the statement to fail.
Requires membership in the db_owner fixed database role.
A. Changing a capture job
The following example updates the
@maxtrans parameters of a capture job in the
AdventureWorks2012 database. The other valid parameters for a capture job,
@pollinginterval, are omitted; their values are not modified.
USE AdventureWorks2012; GO EXECUTE sys.sp_cdc_change_job @job_type = N'capture', @maxscans = 1000, @maxtrans = 15; GO
B. Changing a cleanup job
The following example updates a cleanup job in the
AdventureWorks2012 database. All valid parameters for this job type, except @threshold, are specified. The value of @threshold is not modified.
USE AdventureWorks2012; GO EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 2880; GO