SQL CDC清理作业（Cleanup Job）保留时间无法正常工作求助

我来帮你一步步排查这个CDC清理作业的问题——这类情况我碰到过好几次，咱们从几个关键方向入手：

1. 先确认清理作业的调用参数是否正确 #

默认的CDC清理作业是通过sys.sp_cdc_cleanup_change_table存储过程执行的，最常见的坑就是保留时间的单位是分钟，很多人会误设成小时/天导致清理不生效。

先查询你的CDC清理作业的具体调用命令，看看参数是否符合预期：

SELECT j.name AS JobName, js.step_name, js.command
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
WHERE j.name LIKE '%CDC%Cleanup%'

检查命令里的@retention_period值，比如要保留7天的话，这个值应该是7*24*60=10080，而不是7或者168。

2. 检查清理作业是否真的在运行，有没有报错 #

有时候作业可能因为调度关闭、权限问题或者执行报错没正常运行，导致数据没被清理。

查询作业的执行历史，看看最近的运行状态：

SELECT j.name, h.run_date, h.run_time, h.run_status, h.message
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE j.name LIKE '%CDC%Cleanup%'
ORDER BY h.run_date DESC, h.run_time DESC

• run_status=0表示执行失败，看message字段的错误信息就能定位问题（比如权限不足、锁冲突）
• 如果没有最近的执行记录，检查作业的调度是否开启，作业代理账户有没有访问CDC系统表的权限。

3. 验证数据库级别的CDC保留时间配置 #

数据库本身也有CDC保留时间的默认值，虽然作业参数优先级更高，但先确认这个值是否符合预期：

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

这里的cdc_retention_period同样是分钟单位，如果这个值和你预期的保留时间不符，可以用sys.sp_cdc_change_job修改：

EXEC sys.sp_cdc_change_job 
    @job_type = N'cleanup',
    @retention = 10080; -- 7天，单位分钟
GO
-- 重启作业生效
EXEC sys.sp_cdc_stop_job @job_type = N'cleanup';
EXEC sys.sp_cdc_start_job @job_type = N'cleanup';

4. 检查是否有长时间运行的事务阻塞清理 #

CDC清理作业的低水位线由数据库中最早的未提交事务决定——如果有大事务一直没提交，清理作业无法删除早于这个事务开始时间的变更数据，会导致数据一直保留。

先查询活跃的长期事务：

SELECT transaction_id, begin_time, transaction_name
FROM sys.dm_tran_active_transactions
ORDER BY begin_time ASC

再查看目标表CDC的低水位线和最后清理时间：

SELECT capture_instance, low_water_mark, last_cleanup_date
FROM cdc.change_tables
WHERE source_object_id = OBJECT_ID('dbo.Person')

如果last_cleanup_date很久没更新，且存在早于保留时间的未提交事务，那就是这个问题了，需要结束这些长期事务（谨慎操作，确认事务可以终止）。

5. 手动执行清理存储过程测试 #

如果上面的排查都没问题，手动调用清理存储过程，看是否能正常执行并返回错误信息：

-- 先确认你的capture_instance名称，默认是schema_table格式
EXEC sys.sp_cdc_cleanup_change_table
    @capture_instance = 'dbo_Person',
    @low_water_mark = NULL,
    @retention_period = 1440; -- 测试保留1天

执行后如果有报错，直接根据错误提示修复即可；如果执行成功但数据还是没清理，那可能是你的变更数据本身还没到保留期限，或者低水位线没更新。

如果这些步骤还没解决问题，可以补充以下信息：

• 数据库的版本（比如SQL Server 2019/2022）
• 清理作业执行的具体错误日志
• CDC变更表的大小变化情况

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