18

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.

magnattic
  • 12,638
  • 13
  • 62
  • 115
Eedoh
  • 5,818
  • 9
  • 38
  • 62

3 Answers3

36

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.

Eedoh
  • 5,818
  • 9
  • 38
  • 62
10

If you want to retain the CDC data indefinitly, you can simply disable the CDC cleanup job:

  1. Open SQL Server Management Studio and connect to your database server
  2. In the object explorer, expand “<instance> | SQL Server Agent | Jobs”
  3. Find the job for cleanup named “cdc.<database name>_cleanup”.
  4. Right-click the job and select "disable"

Here is a sample picture of where to find the option:

How to disable CDC Cleanup

After you disabled the cleanup job, the CDC data will no longer get removed after a certain time interval.

magnattic
  • 12,638
  • 13
  • 62
  • 115
  • What's the difference between Stop Job and Disable in this case? – Mark13426 May 23 '14 at 19:14
  • 3
    My guess: stop halts it until the next Server restart, disable halts it permanently. – magnattic May 23 '14 at 20:08
  • Thanks Atticae for the answer. Have a query on CDC...can we enable CDC for a particular DML operation - only for update? – Rajesh Bhat Jan 20 '17 at 10:54
  • 2
    @Mark13426 - "Stop Job" stops a job that is currently running. It will remain enabled, and run again at its next scheduled time. "Disable" will prevent the job form running at its next scheduled time. If a currently running job is disabled, the current run will continue (unless you also use "Stop Job" on it, of course :-) ). – RDFozz Mar 24 '17 at 17:13
8

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
Siva
  • 422
  • 6
  • 16