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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



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

Leave a Comment


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