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.
(This blog was originally posted on March 7, 2012.)