SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server 2008 - CDC Retention

I had done a little work with CDC last year, experimenting with the way that it handles changes in your database. Someone had asked me the question about the retention period since the default of 3 days was not sufficient for their environment.

BOL lists the default retention period as three days, and this is based on a cleanup job that goes through and removes data from the tables. However you can alter that with a stored procedure: sys.sp_cdc_change_job.

There are a number of parameters for this procedure, most dealing with managing the load of the cdc jobs. You can alter the cleanup or capture jobs with this, but for the retention period, you want to change the cleanup job.

@retention is the parameter that indicates the number of minutes that change rows are going to be retained in the CDC tables. If you pass in a NULL, the old retention period is retained. You can include the number of minutes you’d like, up to 52494800 (100 years). I would recommend you choose something less.

The other thing to be aware of is that you have various intervals that are set, and also a @threshold, which determines how many rows are deleted on each scan. If you limit the cleanup to something less than continuously operating, then be aware that if @threshold is too low, it might not delete all the rows. If you are changing these values to manage the load of deletions, be sure that you are monitoring how many rows are changing as it might grow over time.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by _UB on 15 October 2012


I did not have much luck with the retention period setting in the msdb.dbo.cdc_jobs table. I am performing more tests, but I wanted to check with you to see if you performed any tests just on this setting.



Leave a Comment

Please register or log in to leave a comment.