We have CDC enabled on SQL Server 2008 with retention re-configured to a number other than the default 3 days (or something similar).
When we first configured it (with default options), it worked fine. But since the day we changed the retention period from default to something like 25 days, it stopped working correctly. It would run for many hours (15 to 20 hours). So, after a few days we disabled the job (not good, but we had other priorities to deal with than the data retention).
Now after 7 months, the disk is close to getting full. So we need to re-configure it and start purging data. A few days ago, just to test how it works, I ran a test run. I checked the timestamp of the oldest LSN and added a month to it and changed the retention period to something like 8 months. So when the clean up job runs, it should come back with success immediately, as there is no data older than 7 months. But it does not. Its still runs for many hours.
Now I am starting to wonder if the retention period (column) in msdb.dbo.cdc_jobs table works or not. Aaron Bertrand mentioned in his blog that he did not have perfect success with this setting. Maybe this setting is not used by the Clean up job.
Before I run some intensive tests on a Development machine, I wanted to check with you guys.