Is CDC - Change Data capture has any retention limit ? Can we setup for long period?

• sram24_mca

  SSCrazy

  Points: 2633

  More actions

  September 27, 2017 at 10:50 pm

  #339549

  SQL SERVER 2012 - Change Data Capture feature has any limitation on Retention limit. Please suggest the good article to understand more on these features...

  Regards,
  Ram

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  September 28, 2017 at 8:20 am

  #1961363

  sram24_mca - Wednesday, September 27, 2017 10:50 PM

  SQL SERVER 2012 - Change Data Capture feature has any limitation on Retention limit. Please suggest the good article to understand more on these features...

  Regards,
  Ram

  You set the retention through the job sp_cdc_change_job. The documentation for the job lists the maximum value is 52494800 (100 years). If you need more than that, I would guess you could disable the job. But you hopefully would upgrade before that time and the job as well as the business requirements might change.
  Documentation for the job:
  sys.sp_cdc_change_job (Transact-SQL)

  Here is another article you can read about the retention period:
  SQL SERVER – Working with Change Data Capture – Part 4

  Sue