http://stackoverflow.com/questions/3288391/how-the-cdc-retention-value-can-be-changed-for-the-cleanup-job I just discovered that the default retention value is 4320 minutes = 72 hours = 3 days.
It should be configurable by using sp_cdc_change_job @job_type='cleanup', @retention=minutes The maximum value is 52494800 (100 years). If specified, the value must be a positive integer. Retention is valid only for cleanup jobs. http://sqlblog.com/blogs/allen_white/archive/2012/03/07/set-and-verify-the-retention-value-for-change-data-capture.aspx 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' USE AdventureWorks2012; GO EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 2880; GO
