If I do something like this:
USE AdventureWorks2012; GO EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 52494800; -- (100 years) GO
The records in the change tables will never (until year 2119) be deleted.
Also, I'm applying a script like this to move data from an old instance to a new one every time a new column is added to the tracked table, and I know that if there are many records, it can take a little bit of time to copy them to the new change table.
So, I'm planning to keep the change history forever on these change tables, and never purge the tables using the cleanup job. But the documentation says:
Data that is deposited in change tables will grow unmanageably if you do not periodically and systematically prune the data
If CDC had been in plance since 2007 in my DB, the biggest common change table will contain ~20K rows in 12 years, the source table is replicated to another server, so, having said that:
- What does 'grow unmanageably' means for me?
- Will it have vey negative impact on the performance or storage in the DB? Maybe an impact on Replication performance?
- Am I just fine doing CDC with no cleanup job at all? or
- Is it a better alternative to copy the data to a reporting DB using a SSIS package or a job runing a stored procedure?
- If so, is there a simple way to handle the creation of new columns in the destination table based on new columns added in the change tables?
- Does the answer change if instead of 20K rows in one change table there are 1 million?