0

I have enabled cdc and using it , it works fine now the problem is that we have many tables in a database and also we have many databases so how to configure cdc cleanup job to run again and again after a specific time , I read this article tried what he said but its not working for me .

I also tried this SP sp_MScdc_cleanup_job , I executed this it clean all data but i need it to be automatic

Let suppose i want to run it every minute just for check that its running or not , What should i do ?

3

After you enable the CDC on a database 2 jobs are created for that database:

  • cdc.DBName_capture (which will start the change data capture collection agent)
  • cdc.DBName_cleanup (which will clean up the change tables periodically)
    • the default retention is 3 days (data older than 3 days is removed)
    • the default schedule for this job is daily at 02:00 AM

The default retention period (how many days worth of data is kept before removal) and the threshold value (how many rows at once to clear in one execution) can be found in the table msdb.dbo.cdc_jobs, so any configuration for the cleanup can start here. Also, changing the schedule of the cleanup job is a trivial thing to do, as it's not different than any other job in this aspect.

So what you need to do is go to all the cleanup jobs of your CDC enabled databases and tune and schedule them as needed.

References:

  • Fine , i understood now and its working for me . Can you also please help me i want to know that can i ensure that whenever cleanup job executes it only delete data which is older then 2 months ? . I mean after cleanup job i want to see last 2 months data – Ancient Dec 24 '14 at 4:27
  • That's the info you can find in the table cdc_jobs in the msdb database - column retention. The default value is 4320 - expressed in minutes = 3 days. Change this value to a value like 129600 (90 days) or something that suits you more. – Marian Dec 24 '14 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.