I'm in a SQL Server 2012 environment, and I have CDC enabled on a table. To sum, the table is used to populate a data warehouse (basically, the content of the table is duplicated elsewhere).
However, I just noticed that we are losing a lot of data because CDC was enabled with the default setting of 3 days. I know how to write a query that enables a CDC with a NEW/DIFFERENT retention period. But that's not the task, the task is that I must be able to change the current CDC setting. So, is there a way to ALTER the retention period?
Also, additionally, is there a way to prevent CDC from deleting expired records all in all? meaning, is there a way to implement a flag that prevents the CDC to delete any records UNLESS that record has already been transferred/moved.
Thank you very much!
sys.sp_cdc_change_job is what you're looking for to change the retention.
As to your second question, there is no way for CDC to know what rows you've processed and not. So, you have to set your retention period such that you have enough time to process the records that have accumulated since the ETL job last ran. The typical workflow that uses CDC runs on a regular periodic basis (e.g. daily, weekly). So I smell something odd when you say that you're losing data (unless the current retention period is set lower than the time between ETL runs).