How the cdc retention value can be changed for the cleanup job?
I'm implementing a logging feature on a asp.net mvc2 application, that uses SqlServer2008 as a database and Entity Framework as a data model.
I enabled CDC feature of SqlServer and it's logging changes well, but I just noticed that some of the old logging data is erased.
Does anyone know what's default period CDC keeps records, and does anyone know how could I set it to indefinite value.
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.
Here's the link to the more detail explanation of sp_cdc_change_job procedure
Hope this will help someone else, too :D.
If you want to retain the CDC data indefinitly, you can simply disable the CDC cleanup job:
- Open SQL Server Management Studio and connect to your database server
- In the object explorer, expand “<instance> | SQL Server Agent | Jobs”
- Find the job for cleanup named “cdc.<database name>_cleanup”.
- Right-click the job and select "disable"
Here is a sample picture of where to find the option:
After you disabled the cleanup job, the CDC data will no longer get removed after a certain time interval.
By default it deletes anything older than 3 days, to change the default value to 14 days use the following script
use <Your database> go SELECT ([retention])/((60*24)) AS Default_Retention_days ,* FROM msdb.dbo.cdc_jobs go EXEC <Your database>.sys.sp_cdc_change_job @job_type=N'Cleanup' ,@retention=20160 -- <60 min *24 hrs * 14 days > go SELECT ([retention])/((60*24)) AS Default_Retention_days ,* FROM msdb.dbo.cdc_jobs Go