Change data capture uses a retention based cleanup strategy to manage change table size. you can check default retention in minutes using below syntax
select *,retention from msdb.dbo.cdc_jobs
Cleanup Job start cleaning process every night, by default it will clean data of three days older.
You can update this retention value and cleanup job as below.
USE [msdb]
GO
Declare @Job_Id UniqueIdentifier
SELECT @Job_id = job_id From msdb.dbo.sysjobs where name = 'cdc.DatabaseName_cleanup'
Declare @Command Varchar(MAX)
Set @Command = N'Declare @EndLSN as Binary(10)
SELECT @EndLSN = GetDate()
if @EndLSN is not null
UPDATE msdb.dbo.cdc_jobs Set retention = isnull(DateDiff(MI, sys.fn_cdc_map_lsn_to_time(@EndLSN), GetDate()),0)+1440 where job_id = ''' + CONVERT(Varchar(100), @Job_Id) + '''
GO
sys.sp_MScdc_cleanup_job'
EXEC msdb.dbo.sp_update_jobstep @job_id=@Job_id, @step_id=1 ,
@command = @Command
GO
select *,retention from msdb.dbo.cdc_jobs
Cleanup Job start cleaning process every night, by default it will clean data of three days older.
You can update this retention value and cleanup job as below.
USE [msdb]
GO
Declare @Job_Id UniqueIdentifier
SELECT @Job_id = job_id From msdb.dbo.sysjobs where name = 'cdc.DatabaseName_cleanup'
Declare @Command Varchar(MAX)
Set @Command = N'Declare @EndLSN as Binary(10)
SELECT @EndLSN = GetDate()
if @EndLSN is not null
UPDATE msdb.dbo.cdc_jobs Set retention = isnull(DateDiff(MI, sys.fn_cdc_map_lsn_to_time(@EndLSN), GetDate()),0)+1440 where job_id = ''' + CONVERT(Varchar(100), @Job_Id) + '''
GO
sys.sp_MScdc_cleanup_job'
EXEC msdb.dbo.sp_update_jobstep @job_id=@Job_id, @step_id=1 ,
@command = @Command
GO
No comments:
Post a Comment