locked
Stoping CDC cleanup job IN CDC RRS feed

  • Question

  • Hi Experts,

         I am planning to manage historical data for my OLTP database. for this I have enable the CDC for database and for the table, its working fine. 

       But the CDC cleanup job will clean the data after three days that I don't want. I want to maintain this historical data I don't want to delete this. I am thinking to disable the CDC cleanup job is it good solution or we have any other way to make it done.

     Thanks in advance for help.

        

     


    Regards Vikas Pathak

    Monday, November 18, 2013 7:11 AM

Answers

All replies

  • It is easy to adjust the retention period CDC uses, but you probably want to let it do what it wants to do.

    Most people want to retain some or all CDC data permanently, but what you are supposed to do is copy it out of the retention tables and into your own tables before the three days!

    If you tried to retain it in the CDC tables, it would probably slow down the process to nothing.

    Josh

    • Proposed as answer by Sofiya Li Tuesday, November 19, 2013 8:23 AM
    Monday, November 18, 2013 8:02 AM
  • I too want to keep CDC data permanently. How I can?

    Regards Vikas Pathak


    Monday, November 18, 2013 9:01 AM
  • As already suggested it propably slow down the process but incase you want to change retention than use below command

    EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 349082;
    -- or
    UPDATE msdb.dbo.cdc_jobs SET retention = '349082' WHERE job_type = 'cleanup'

    Can also refer below links:

    http://stackoverflow.com/questions/10820827/t-sql-change-data-capture-log-cleanup
    http://msdn.microsoft.com/en-us/library/bb510748.aspx


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    • Proposed as answer by Sofiya Li Tuesday, November 19, 2013 8:23 AM
    • Marked as answer by vikas kumar pathak Wednesday, November 20, 2013 9:32 AM
    Monday, November 18, 2013 9:47 AM