Edit

Share via

sys.sp_cdc_add_job (Transact-SQL)

  • Article
  • 08/23/2024

Applies to: SQL Server

Creates a change data capture cleanup or capture job in the current database.

Transact-SQL syntax conventions

Syntax

sys.sp_cdc_add_job [ @job_type = ] N'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 = ] N'job_type'

Type of job to add. @job_type is nvarchar(20) and can't be NULL. Valid inputs are capture and cleanup.

[ @start_job = ] start_job

Flag indicating whether the job should be started immediately after it's added. @start_job is bit with a default of 1.

[ @maxtrans ] = max_trans

Maximum number of transactions to process in each scan cycle. @maxtrans is int with a default of 500. If specified, the value must be a positive integer.

@maxtrans 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 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 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, the sp_cdc_scan job executes up to @maxscans scans of the log, processing up to @maxtrans transaction during each scan, and then exits.

@continuous is valid only for capture jobs.

[ @pollinginterval ] = polling_interval

Number of seconds between log scan cycles. @pollinginterval is bigint with a default of 5.

@pollinginterval is valid only for capture jobs when @continuous is set to 1. If specified, the value must be greater than or equal to 0 and less than 24 hours (up to 86399 seconds). If a value of 0 is specified, there's 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. @threshold is bigint with a default of 5000.

Return code values

0 (success) or 1 (failure).

Result set

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 isn't required or 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 sys.sp_cdc_help_jobs. To change the configuration of a job, use sys.sp_cdc_change_job.

Permissions

Requires membership in the db_owner fixed database role.

Examples

A. Create 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 AdventureWorks2022;
GO
EXEC sys.sp_cdc_add_job @job_type = N'capture';
GO

B. Create a cleanup job

The following example creates a cleanup job in the AdventureWorks2022 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 AdventureWorks2022;
GO

EXEC sys.sp_cdc_add_job
    @job_type = N'cleanup',
    @start_job = 0,
    @retention = 5760;

Related content