THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Set and Verify the Retention Value for Change Data Capture

Last summer I set up Change Data Capture for a client to track changes to their application database to apply those changes to their data warehouse. The client had some issues a short while back and felt they needed to increase the retention period from the default 3 days to 5 days. I ran this query to make that change:

sp_cdc_change_job @job_type='cleanup', @retention=7200

The value 7200 represents the number of minutes in a period of 5 days. All was well, but they recently asked how they can verify that the change was made. Well, it turns out the values are stored in the msdb database, in a table called dbo.cdc_jobs. I put together this quick query to allow them to test any database with CDC enabled, to find the retention value:

SELECT [retention]
  FROM [msdb].[dbo].[cdc_jobs]
  WHERE [database_id] = DB_ID()
  AND [job_type] = 'cleanup'

It's simple, but without knowing where to look, it can be confusing.


Published Wednesday, March 07, 2012 9:14 PM by AllenMWhite



Thomas LeBlanc said:

thanks, needed something to read and execute this morning.


August 8, 2012 8:51 AM

Gord Cross said:

Thanks. Needed this.

April 18, 2013 6:01 PM

Manish said:

Great to find information here.....

May 21, 2014 9:57 PM

Jon said:

That query didn't return a retention value, but the following does:

select * from msdb.dbo.cdc_jobs

September 15, 2014 2:12 PM

Scott R. said:

Just ran across this post.

I had similar needs some time back, and came up with a SQL statement for an enhanced display of the CDC configuration parameters:

-  Descriptive columns names (easier to understand than the short column names)

-  DB name of CDC DB (versus only DB ID)

-  CDC retention shown in the native minutes and also converted to hours and days (so that you don't have to rely on mental math for this result - easier to cross-check)

-  Information related to the two CDC jobs (capture and cleanup, as subsets of the configuration parameters are related to one or the other job)

The SQL script is included below - enjoy!


Cast(@@ServerName As varchar(20)) AS [DB Server Instance],

DB_Name(database_id) As [DB Name],

Stuff(job_type, 1, 1, Upper(SubString(job_type, 1, 1))) As [Job Type], As [Job Name],

sj.description As [Job Description],

SUser_Name(sj.owner_sid) As [Job Owner],

Case When job_type = 'capture' Then ''

Else Cast(retention As varchar(20)) + ' minutes' End As [CDC Retention],

Case When job_type = 'capture' Then ''

Else Cast(Cast((retention + 0.) / 60. As decimal(9, 2)) As varchar(20)) + ' hours' End As [CDC Retention],

Case When job_type = 'capture' Then ''

Else Cast(Cast((retention + 0.) / (60. * 24.) As decimal(9, 2)) As varchar(20)) + ' days' End As [CDC Retention],

Case When job_type = 'capture' Then ''

Else Cast(threshold As varchar(20)) + ' rows' End As [Threshold (per delete SQL statement)],

Case When job_type = 'capture'

Then Cast(continuous As varchar(5)) + ' - '

+ Case When continuous = 1 Then 'Yes' Else 'No' End

Else '' End As [Continuous?],

Case When job_type = 'capture' Then Cast(maxtrans As varchar(20)) + ' transactions'

Else '' End As [Max. Transactions per scan cycle],

Case When job_type = 'capture' Then Cast(maxscans As varchar(20)) + ' scans'

Else '' End As [Max. # Scans],

Case When job_type = 'capture' Then Cast(pollinginterval As varchar(20)) + ' seconds'

Else '' End As [Polling Interval - between log scan cycles],



From msdb.dbo.cdc_jobs cj

Left Join msdb.dbo.sysjobs sj

On (cj.job_id = sj.job_id)

Scott R.

March 17, 2015 1:34 PM

Rafael Bahia said:

Thank you.

March 10, 2016 11:15 AM
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement