Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server
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 sys.sp_cdc_help_jobs.
Transact-SQL syntax conventions
sys.sp_cdc_change_job [ [ @job_type = ] N'job_type' ]
[ , [ @maxtrans = ] max_trans ]
[ , [ @maxscans = ] max_scans ]
[ , [ @continuous = ] continuous ]
[ , [ @pollinginterval = ] polling_interval ]
[ , [ @retention ] = retention ]
[ @threshold = ] 'delete threshold'
[ ; ]
Type of job to modify. @job_type is nvarchar(20) with a default of capture. Valid inputs are capture and cleanup.
Maximum number of transactions to process in each scan cycle. @maxtrans 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.
Maximum number of scan cycles to execute in order to extract all rows from the log. @maxscans is int, with a default of NULL, which indicates no change for this parameter.
@max_scan is valid only for capture jobs.
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 is
1, the sys.sp_cdc_scan job scans the log and processes up to (@maxtrans * @maxscans) transactions. It then waits the number of seconds specified in @pollinginterval before beginning the next log scan.When @continuous is
0, thesp_cdc_scanjob executes up to @maxscans scans of the log, processing up to @maxtrans transactions during each scan, and then exits.If @continuous is changed from
1to0, @pollinginterval is automatically set to0. A value specified for @pollinginterval other than0is ignored.If @continuous is omitted or explicitly set to
NULLand @pollinginterval is explicitly set to a value greater than0, @continuous is automatically set to1.
@continuous is valid only for capture jobs.
Number of seconds between log scan cycles. @pollinginterval is bigint, with a default of NULL, which indicates no change for this parameter.
@pollinginterval is valid only for capture jobs when @continuous is set to 1.
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.
Maximum number of delete entries that can be deleted using a single statement on cleanup. @threshold is bigint, with a default of NULL, which indicates no change for this parameter. @threshold is valid only for cleanup jobs.
0 (success) or 1 (failure).
None.
If a parameter is omitted, the associated value in the dbo.cdc_jobs table isn't 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 causes the statement to fail.
Changes to a job don't take effect until the job is stopped by using sys.sp_cdc_stop_job and restarted by using sys.sp_cdc_start_job.
Requires membership in the db_owner fixed database role.
The following example updates the @job_type, @maxscans, and @maxtrans parameters of a capture job in the AdventureWorks2022 database. The other valid parameters for a capture job, @continuous and @pollinginterval, are omitted; their values aren't modified.
USE AdventureWorks2022;
GO
EXECUTE sys.sp_cdc_change_job
@job_type = N'capture',
@maxscans = 1000,
@maxtrans = 15;
GO
The following example updates a cleanup job in the AdventureWorks2022 database. All valid parameters for this job type, except @threshold, are specified. The value of @threshold isn't modified.
USE AdventureWorks2022;
GO
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = 2880;
GO