CDC配置保留期后旧记录未删除问题排查咨询

以下是针对你的场景的具体排查方向：

• 验证CDC保留期配置的准确性
  执行以下查询确认每个CDC捕获实例的保留期设置是否正确（2天对应2880分钟）：

  EXEC sys.sp_cdc_help_change_data_capture;
  

  检查结果中的retention_period和retention_period_unit列，确保单位是分钟且数值为2880。同时确认数据库的CDC启用状态：

  SELECT name, is_cdc_enabled FROM sys.databases WHERE name = '你的数据库名';
  

• 深入检查清理作业的执行细节

  • 查看SQL代理作业的步骤执行日志（不要仅看作业整体状态），确认是否有被忽略的警告或跳过逻辑，部分作业可能通过TRY/CATCH这类错误处理隐藏了实际报错。
  • 核对作业的TSQL代码：如果作业调用了不存在的存储过程却未报错，大概率是代码中存在错误吞服逻辑，或者调用语句被注释、未实际执行。可以手动执行作业中的脚本片段，验证是否会抛出错误。

• 确认作业执行的权限与上下文

  • 检查作业运行账户的权限：必须具备db_owner角色权限，或至少拥有CDC系统表的DELETE权限、sys.sp_cdc_cleanup_change_table的EXECUTE权限。权限不足可能导致清理操作静默失败，但作业仍返回"成功"。
  • 确认作业的调度频率：如果作业运行间隔超过2天，可能还未触发针对旧记录的清理逻辑，检查作业的调度设置是否符合预期（比如每日运行）。

• 手动测试CDC清理逻辑
  针对单个捕获实例手动执行清理存储过程，验证是否能删除旧记录并查看报错：

  -- 替换为你的捕获实例名称
  EXEC sys.sp_cdc_cleanup_change_table 
      @capture_instance = 'dbo_你的表名_CT',
      @low_water_mark = NULL;
  

  同时查询CDC系统表sys.cdc_jobs，确认清理作业的配置参数：

  SELECT * FROM sys.cdc_jobs WHERE job_type = 'cleanup';
  

  检查retention值是否为2880，threshold（每次清理的最大删除行数）是否设置合理（默认5000，若值过小可能需要多次运行才能清理完旧记录）。

• 验证CU更新的实际生效状态
  执行以下语句确认SQL Server版本是否确实为CU31及以上：

  SELECT @@VERSION;
  

  比如SQL Server 2019 CU31对应的版本号为15.0.4316.3，如果版本不符，说明CU安装未成功或实例未重启生效。同时确认该CU是否包含CDC清理相关的修复补丁。

• 排查长期运行事务对清理的阻塞
  长期未提交的事务会阻止CDC最小LSN的推进，导致旧记录无法被清理：

  • 执行DBCC OPENTRAN查看是否存在长时间运行的活动事务。
  • 查询当前捕获实例的最小LSN并转换为时间，判断是否被事务阻塞：

    -- 替换为你的捕获实例名称
    SELECT sys.fn_cdc_map_lsn_to_time(sys.fn_cdc_get_min_lsn('dbo_你的表名_CT')) AS min_cdc_time;
    

    如果返回的时间远早于保留期，说明存在事务阻止了LSN的更新，需要排查并结束长期运行的事务。

内容的提问来源于stack exchange，提问作者JS JHA