• your settings for CDC were probably set to keep data for 100 years or something,a nd now you are seeing some performance inpact;

    I setup CDC on a few tables, but have not had to fiddle with them since, so i'm a little rusty.

    As i remember it, you need to change your setting on how long you want to keep the data:

    http://msdn.microsoft.com/en-us/library/bb510748.aspx

    exec sp_cdc_change_job @job_type='cleanup', @retention=integerminutes

    so instead of the max value of 52494800(100 years? 36454 days actually), change it to say, 2 years:

    select 365 * 2 * 24 * 60 --1051200 minutes? check my math!

    exec sp_cdc_change_job @job_type='cleanup', @retention=1051200

    now you should be able to call the parameterless cleanup procedure, and it will start cleaning the cdc tables of older data:

    EXEC sp_MScdc_cleanup_job

    I think that's what you need to do, but some peer review would help, of course; see what some other folks say too!

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!