Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to change CDC automatic cleanup process? Expand / Collapse
Author
Message
Posted Saturday, July 17, 2010 3:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 1, 2013 2:51 AM
Points: 283, Visits: 312
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
Post #954329
Posted Saturday, July 17, 2010 10:20 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 6, 2014 4:17 PM
Points: 382, Visits: 1,141


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://social.msdn.microsoft.com/Forums/en/sqldatawarehousing/thread/d31167cc-ddbb-4e1e-b266-105569ff9bb2
http://technet.microsoft.com/en-us/library/bb510449.aspx






Sankar Reddy | http://SankarReddy.com/
Post #954395
Posted Saturday, July 17, 2010 11:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:05 PM
Points: 11,185, Visits: 11,070
Sankar Reddy (7/17/2010)
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.

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #954404
Posted Saturday, July 17, 2010 3:45 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 6, 2014 4:17 PM
Points: 382, Visits: 1,141
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.


Sankar Reddy | http://SankarReddy.com/
Post #954449
Posted Saturday, July 17, 2010 7:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:05 PM
Points: 11,185, Visits: 11,070
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #954461
Posted Thursday, January 2, 2014 5:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 3:54 AM
Points: 59, Visits: 159
@ 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
FROM msdb.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
FROM msdb.dbo.cdc_jobs
GO

Thanks,
Prabhu
(Hope this may helps...)
Post #1527044
Posted Thursday, January 2, 2014 2:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 2, 2014 2:54 PM
Points: 1, Visits: 4
This is Exactly what i was looking for.

Thanks For the nice post Prabhu
Post #1527290
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse