SQL Server Management Studio – Why Won’t SSMS Remember My Tables?

I answered this question in my blog at Sqltimes.wordpress.com As far as I know, the only way to configure retention period is by updating the retention column in the msdb.dbo.cdc_jobs table. You want to update the retention column of the record with 'cleanup' in job_type column. It could be done in two ways:

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

If you have more than one CDC running, then using the appropriate CDC instance name.

A Little more info:

When the CDC cleanup job runs, it purges all data in the CDC tables up to this date. You can stop the CDC cleanup job any time while it is running. When you start it, it will pick up from where it left off. So if you stopped the job while it is running today and start to run it tomorrow it will start from where it left off yesterday and perform purge until the retention period mentioned in the msdn.dbo.cdc_jobs table.

CDC is pretty robust technology.

Hope it help, _UB

Well, I'm a little ashamed... SQL Server 2014 SP1 was not installed on my client machine. Doing so seems to fix the problem with SSMS Intellisense.