T-SQL Change Data Capture log cleanup

Question

I have enabled CDC on few tables in my SQL server 2008 database. I want to change the number of days I can keep the change history.

I have read that by default change logs are kept for 3 days, before they are deleted by sys.sp_cdc_cleanup_change_table stored proc.

Does anyone know how I can change this default value, so that I can keep the logs for longer.

Thanks

Answer 1

BTW: If you have AlwaysOn availability groups configured, you have to remember to update the CDC configurations on the NEW Primary after failing over! We learned the hard way after updating the retention to a value LONGER than the default 3 days, we lost those changes after failing over!

Alternatively, you could proactively update the SECONDARY servers, by comparing PRIMARY/SECONDARY config values and then updating msdb.dbo.cdc_jobs directly on the secondary to match. I do not know if Microsoft officially supports this, but they don't prevent direct updates to the msdb tables, either.

Here's a sample script you can schedule on replica in an AlwaysON group. It will check the current configuration on the PRIMARY and synch changes to the SECONDARY server...

---

CREATE OR ALTER PROCEDURE sp_cdc_config_sync 

/**********************************************
--PURPOSE: This proc runs on a secondary replica in an AlwaysOn group and checks for CDC Config changes on the PRIMARY
--         If changes are detected, it will syncronize those changes by updating msdb.dbo.cdc_jobs localluy
--         You can run this interactively, but it is intended to be called by the scheduled job for maint.dbo.sp_cdc_hadr_watchdog

-- Author Dan Carollo, 2024. 

***********************************************/
AS
SET NOCOUNT ON  

--We assume if this is the primary for even ONE database, then don't run 
IF NOT EXISTS(
SELECT name,is_cdc_enabled,
sys.fn_hadr_is_primary_replica ( name) as IsPrimary
FROM sys.databases 
WHERE is_cdc_enabled=1
AND sys.fn_hadr_is_primary_replica ( name) =1
) 
IF EXISTS (SELECT 1 FROM sys.databases WHERE is_cdc_enabled=1)
BEGIN

    DECLARE @PrimaryReplica sysname  
    DECLARE @PrimaryConnectString VARCHAR(1000)  
    DECLARE @OPENROWSETString VARCHAR(4000)



    --Get the primary replica
    SET @PrimaryReplica=(SELECT TOP 1 primary_replica from sys.dm_hadr_availability_group_states where synchronization_health_desc='HEALTHY')  


    SET @PrimaryConnectString='Server='+@PrimaryReplica+';Database=msdb;TrustServerCertificate=Yes;Trusted_Connection=Yes;MultiSubnetFailover=Yes;'

    SELECT @PrimaryConnectString

    SET @OPENROWSETString='
    SELECT *
    FROM OPENROWSET(
        ''SQLNCLI'',
        '''+@PrimaryConnectString+''',
        ''IF EXISTS (SELECT 1 FROM sys.databases WHERE is_cdc_enabled=1) SELECT 
    @@Servername as ServerName,
    sd.name as DBName,
    cdc.database_id,
    cdc.job_type,
    cdc.job_id,
    cdc.maxtrans,
    cdc.maxscans,
    cdc.continuous,
    cdc.pollinginterval,
    cdc.retention,
    cdc.threshold
    FROM msdb.dbo.cdc_jobs cdc
    JOIN sys.databases sd
    ON cdc.database_id=sd.database_id''
    )'

  

    DROP TABLE IF EXISTS #RemoteConfig
    CREATE TABLE #RemoteConfig (
    ServerName varchar(64),
    DBName varchar(64),
    database_id int,
    job_type varchar(12),
    job_id uniqueidentifier,
    maxtrans int,
    maxscans int,
    continuous bit,
    pollinginterval int,
    retention int,
    threshold int
    )

    INSERT INTO #RemoteConfig
    EXEC (@OPENROWSETString);

    DROP TABLE IF EXISTS #LocalConfig
    IF EXISTS (SELECT 1 FROM msdb.dbo.cdc_jobs) 
        SELECT  
        @@Servername as ServerName,
        sd.name as DBName,
        cdc.database_id,
        cdc.job_type,
        cdc.job_id,
        cdc.maxtrans,
        cdc.maxscans,
        cdc.continuous,
        cdc.pollinginterval,
        cdc.retention,
        cdc.threshold
        INTO #LocalConfig 
        FROM msdb.dbo.cdc_jobs cdc
        JOIN sys.databases sd
        ON cdc.database_id=sd.database_id

    --DEBUG
    SELECT 'PRIMARY'as Role,* FROM #RemoteConfig
    SELECT 'SECONDARY' as [Role],* FROM #LocalConfig

    --Will update if there are any unmatching column values for each job type and db
    UPDATE l   
    SET l.maxtrans=r.maxtrans,
        l.maxscans=r.maxscans,
        l.continuous=r.continuous,
        l.pollinginterval=r.pollinginterval,
        l.retention=r.retention,
        l.threshold=r.threshold
    FROM msdb.dbo.cdc_jobs as l
    JOIN #RemoteConfig r
        ON db_name(l.database_id) = r.DBname
        AND l.job_type = r.job_type
    WHERE
        r.maxtrans <> l.maxtrans OR
        r.maxscans <> l.maxscans OR
        r.continuous <> l.continuous OR
        r.pollinginterval <> l.pollinginterval OR
        r.retention <> l.retention OR
        r.threshold <> l.threshold

    IF @@ROWCOUNT > 0 PRINT '***CHANGES SYNCHED***' ELSE PRINT '***NO CHANGES***'

    SELECT db_name(msdb.dbo.cdc_jobs.database_id) as DBName, * FROM  msdb.dbo.cdc_jobs

END
ELSE 
BEGIN 
    PRINT '***PRIMARY REPLICA OR CDC NOT ENABLED, SKIPPING CONFIG SYNC***'
    IF EXISTS (SELECT 1 FROM sys.databases WHERE is_cdc_enabled=1)
    SELECT db_name(msdb.dbo.cdc_jobs.database_id) as DBName, * FROM  msdb.dbo.cdc_jobs
END

Answer 2

Two alternative solutions:

1. Drop the cleanup job:

   EXEC sys.sp_cdc_drop_job @job_type = N'cleanup';
   

2. Change the job via sp:

   EXEC sys.sp_cdc_change_job 
   @job_type = N'cleanup',
   @retention = 2880;
   

Retention time in minutes, max 52494800 (100 years). But if you drop the job, data is never cleaned up, the job isn't even looking, if there is data to clean up. In case of wanting to keep data indefinitely, I'd prefer dropping the job.

Answer 3

You need to update the cdc_jobs.retention field for your database. The record in the cdc_jobs table won't exist until at least one table has been enabled for CDC.

-- modify msdb.dbo.cdc_jobs.retention value (in minutes) to be the length of time to keep change-tracked data
update
    j
set
    [retention] = 3679200 -- 7 years
from
    sys.databases d
inner join
    msdb.dbo.cdc_jobs j
        on j.database_id = d.database_id
        and j.job_type = 'cleanup'
        and d.name = '<Database Name, sysname, DatabaseName>';

Replace <Database Name, sysname, DatabaseName> with your database name.