sys.sp_cdc_add_job (Transact-SQL)
Creates a change data capture cleanup or capture job in the current database.
.gif "Topic link icon")
Syntax
sys.sp_cdc_add_job [ @job_type= ] 'job_type'
[ , [ @start_job= ] start_job ]
[ , [ @maxtrans= ] max_trans ]
[ , [ @maxscans= ] max_scans ]
[ , [ @continuous= ] continuous ]
[ , [ @pollinginterval= ] polling_interval ]
[ , [ @retention ] =retention ]
[ , [ @threshold ] ='delete_threshold' ]
Arguments
[ @job_type= ] 'job_type'
Type of job to add. job_type is nvarchar(20) and cannot be NULL. Valid inputs are 'capture' and 'cleanup'.[ @start_job= ] start_job
Flag indicating whether the job should be started immediately after it is added. start_job is bit with a default of 1.[ @maxtrans ] = max_trans
Maximum number of transactions to process in each scan cycle. max_trans is int with a default of 500. 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 10.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 1.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 transaction during each scan, and then exits.
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 5.polling_interval is valid only for capture jobs when continuous is set to 1. If specified, the value cannot be negative and cannot exceed 24 hours. If a value of 0 is specified, there is no wait between log scans.
[ @retention ] **=**retention
Number of minutes that change data rows are to be retained in change tables. retention is bigint with a default of 4320 (72 hours). 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 by using a single statement on cleanup. delete_threshold is bigint with a default of 5000.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None
Remarks
A cleanup job is created using the default values when the first table in the database is enabled for change data capture. A capture job is created using the default values when the first table in the database is enabled for change data capture and no transactional publications exist for the database. When a transactional publication exists, the transactional log reader is used to drive the capture mechanism, and a separate capture job is neither required nor allowed.
Because the cleanup and capture jobs are created by default, this stored procedure is necessary only when a job has been explicitly dropped and must be recreated.
The name of the job is cdc.<database_name>_cleanup or cdc.<database_name>_capture, where <database_name> is the name of the current database. If a job with the same name already exists, the name is appended with a period (.) followed by a unique identifier, for example: cdc.AdventureWorks_capture.A1ACBDED-13FC-428C-8302-10100EF74F52.
To view the current configuration of a cleanup or capture job, use sp_cdc_help_jobs. To change the configuration of a job, use sp_cdc_change_job.
Permissions
Requires membership in the db_owner fixed database role.
Examples
A. Creating a capture job
The following example creates a capture job. This example assumes that the existing cleanup job was explicitly dropped and must be recreated. The job is created using the default values.
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_add_job @job_type = N'capture';
GO
B. Creating a cleanup job
The following example creates a cleanup job in the AdventureWorks2008R2 database. The parameter @start_job is set to 0 and @retention is set to 5760 minutes (96 hours). This example assumes that the existing cleanup job was explicitly dropped and must be recreated.
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_add_job
@job_type = N'cleanup'
,@start_job = 0
,@retention = 5760;
See Also