July 17, 2010 at 3:08 am
Hi
I've read in one of the articles as mentioned below:
In CDC this there is automatic cleanup process that runs at regular intervals. By default the interval is of 3 days but it can be configured. We have observed that, when we enable CDC on the database, there is one additional system stored procedure created with the name sys.sp_cdc_cleanup_change_table which cleans up all the tracked data at interval.
In our application we have created a trigger which posts entries to audit trial table. The data will be retained & will never be deleted.
Can anyone please let me know how to change the CDC cleanup interval from default 3 days to 100 days?
Rgds
Mohan Kumar VS
July 17, 2010 at 10:20 am
[font="Courier New"]
EDIT:
You could use sys.sp_cdc_change_job sp to change the retention using @retention paramter.
[ @retention ] =retention
Number of minutes that change rows are to be retained in change tables. retention is bigint with a default of NULL, which indicates no change for this parameter. The maximum value is 52494800 (100 years). If specified, the value must be a positive integer.
retention is valid only for cleanup jobs.
Mohan,
As far as I know, the custom deletion of data is NOT based on date interval, rather its LSN based and the SP to use is sys.sp_cdc_cleanup_change_table.
Ref:
http://technet.microsoft.com/en-us/library/bb510449.aspx
[/font]
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
July 17, 2010 at 11:21 am
Sankar Reddy (7/17/2010)
[font="Courier New"]As far as I know, the custom deletion of data is NOT based on date interval, rather its LSN based and the SP to use is sys.sp_cdc_cleanup_change_table.[/font]
Isn't that only if you want to run clean-up manually?
As far as I recall, CDC sets up a SQL Agent clean up job which is configurable, defaulting to three days.
It's very much like replication in that respect.
The function sys.fn_cdc_map_lsn_to_time is available to map LSNs to committed times.
I don't recall the exact mechanism for altering the default of three days - maybe it's in the job, maybe it's in the msdb.dbo.cdc_jobs table?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 17, 2010 at 3:45 pm
Paul,
yep, thats an oversight and will update my earlier post. You could use sys.sp_cdc_change_job to specify the retention time. Jonathan Kehayias also confirmed this.
[ @retention ] =retention
Number of minutes that change rows are to be retained in change tables. retention is bigint with a default of NULL, which indicates no change for this parameter. The maximum value is 52494800 (100 years). If specified, the value must be a positive integer.
Thanks for the correction.
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
July 17, 2010 at 7:47 pm
Sankar Reddy (7/17/2010)
You could use sys.sp_cdc_change_job to specify the retention time.
Ah! That was it - sys.sp_cdc_change_job - I didn't have a CDC instance to hand to test so thanks for reminding me of that.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 2, 2014 at 5:06 am
@ Paul & Sankar,
Hi,
My appologize for Replying for a Outdated Post, but I think this might be Useful for the Newbie from this point.
please have the Example of Changing the CDC Cleanup Retention time as below.
USE [Your_DB_Name]
GO
/* 60: refers minutes for 1 Hour & 24: refer the Hours for the Day */
SELECT*,([retention])/((60*24)) AS Default_Retention_days
FROMmsdb.dbo.cdc_jobs
GO
EXEC [Your_DB_Name].sys.sp_cdc_change_job
@job_type=N'Cleanup'
,@retention=43200
GO
SELECT*,([retention])/((60*24)) AS Default_Retention_days
FROMmsdb.dbo.cdc_jobs
GO
Thanks,
Prabhu
(Hope this may helps...)
January 2, 2014 at 2:50 pm
This is Exactly what i was looking for.
Thanks For the nice post Prabhu
June 17, 2016 at 2:21 pm
Thank you Prabhu, very helpful.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply