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:
I think that's what you need to do, but some peer review would help, of course; see what some other folks say too!
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!