Menu
I'm implementing a logging feature on a asp.net mvc2 application, that uses SqlServer2008 as a database and Entity Framework as a data model.
I enabled CDC feature of SqlServer and it's logging changes well, but I just noticed that some of the old logging data is erased.
Does anyone know what's default period CDC keeps records, and does anyone know how could I set it to indefinite value.
594
I just discovered that the default retention value is 4320 minutes = 72 hours = 3 days.
Cleanup Job This job is created automatically by SQL Server to minimize the number of records in the changetables, failing this job execution will be resulting to a larger changetable. This job internally invokes a procedure sys. sp_MScdc_cleanup_job in MSDB database with no parameter.
Before changes to any individual tables within a database can be tracked, change data capture must be explicitly enabled for the database. This is done by using the stored procedure sys. sp_cdc_enable_db. When the database is enabled, source tables can be identified as tracked tables by using the stored procedure sys.
I just discovered that the default retention value is 4320 minutes = 72 hours = 3 days.
It should be configurable by using
sp_cdc_change_job @job_type='cleanup', @retention=minutes
The maximum value is 52494800 (100 years). If specified, the value must be a positive integer. Retention is valid only for cleanup jobs.
Here's the link to the more detail explanation of sp_cdc_change_job procedure
Hope this will help someone else, too :D.
83
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With