sys.sp_cdc_change_job (Transact-SQL)
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 sp_cdc_help_jobs.
Transact-SQL syntax conventions
Syntax
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'
[ ; ]
Arguments
[ @job_type = ] N'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. @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.
[ @maxscans ] = max_scans
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.
[ @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 is
1
, the 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_scan
job executes up to @maxscans scans of the log, processing up to @maxtrans transactions during each scan, and then exits.If @continuous is changed from
1
to0
, @pollinginterval is automatically set to0
. A value specified for @pollinginterval other than0
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 to1
.
@continuous is valid only for capture jobs.
[ @pollinginterval ] = polling_interval
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
.
[ @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. @threshold is bigint, with a default of NULL
, which indicates no change for this parameter. @threshold is valid only for cleanup jobs.
Return code values
0
(success) or 1
(failure).
Result set
None.
Remarks
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 sp_cdc_stop_job and restarted by using sp_cdc_start_job.
Permissions
Requires membership in the db_owner fixed database role.
Examples
A. Change a capture job
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
B. Change a cleanup job
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
Related content
Σχόλια
https://aka.ms/ContentUserFeedback.
Σύντομα διαθέσιμα: Καθ' όλη τη διάρκεια του 2024 θα καταργήσουμε σταδιακά τα ζητήματα GitHub ως μηχανισμό ανάδρασης για το περιεχόμενο και θα το αντικαταστήσουμε με ένα νέο σύστημα ανάδρασης. Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα:Υποβολή και προβολή σχολίων για