July 9, 2013 at 10:14 am
I recently inherited a database that has CDC enabled. There is a custom cleanup process that was written to only remove the records where the operation = 1 or 2, since it appears they wanted to only keep the before and after update statements for reporting.
Here is one of the many problems I face with this....
If I go ahead and turn on the cleanup agent (sys.sp_MScdc_cleanup_job) with a 10 year retention, it removes records from within this timespan.
This table starts out with 5 million records dating back to Nov 2011, then this cleanup job removed 2 million of them which included Dec 2011 and various times for 2012.
I did find out that the database was placed into simple mode on various occasions to shrink the transaction log since there was no DBA in house at the time. :w00t:
Could this be a matter of something being out of sorts within the database when it tries to go back and determine the high and low watermark for the records it thinks it needs to remove? I guess I don't have a clear understanding of how it determines what records to purge from these tables, if I have a 10 year retention set, I would have assumed that the records would have remained untouched. But I know what happens when you assume something... 🙂
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply