Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Creates a change data capture cleanup or capture job in the current database.
Applies to: SQL Server (SQL Server 2008 through current version).
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' ]
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.
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 AdventureWorks2012; 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 AdventureWorks2012 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 AdventureWorks2012; GO EXEC sys.sp_cdc_add_job @job_type = N'cleanup' ,@start_job = 0 ,@retention = 5760;