THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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.

Allen

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

Comments

 

Thomas LeBlanc said:

thanks, needed something to read and execute this morning.

Thomas

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

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement