sql-serverdatabaseloggingchange-data-capture

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.

Solution

  • 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.