MS SQL中如何清理CDC表的所有条目？执行清理后仍有数据是什么原因？

可能的原因及排查解决方法 #

• 清理逻辑的天然保留规则
  sys.sp_cdc_cleanup_change_table只会删除LSN值严格小于你传入的@low_water_mark的记录，你传入的是当前最大LSN，因此最大LSN对应的所有变更记录本身就不会被清理，你查询结果中的记录如果__$start_lsn等于你当时传入的@max_lsn属于正常情况。
• 单次清理的条数限制
  该存储过程默认单次最多删除5000条记录，对应可选参数@threshold的默认值。如果你的CDC变更表中待清理的记录数远大于5000，单次执行无法完成全部清理，需要多次调用该存储过程才能删完所有低于水印的记录。你可以显式指定更大的@threshold值提高单次清理的上限，示例：

DECLARE @max_lsn binary(10);
SET @max_lsn = sys.fn_cdc_get_max_lsn();
Exec sys.sp_cdc_cleanup_change_table
@capture_instance = N'dbo_mytable',
@low_water_mark = @max_lsn,
@threshold = 100000; -- 按需调整单次清理条数

• 清理后有新的变更生成
  如果清理执行完成后，源表dbo.mytable有新的插入、更新、删除操作，CDC捕获作业会自动生成新的变更记录写入变更表，你后续查询到的就是新产生的记录。你可以对比清理前记录的__$start_lsn和查询结果的__$start_lsn确认是否为新增数据。
• CDC消费者水印限制
  如果你的数据库中配置了CDC相关的消费者（比如数据同步、ETL作业注册了CDC消费进度），清理操作会自动以所有消费者的最低水印作为实际清理阈值，你传入的@low_water_mark如果高于该值，不会生效，只会清理低于消费者最低水印的记录。
• 最小LSN未更新验证
  你可以执行以下语句确认清理后的最小LSN是否已经升高：

SELECT sys.fn_cdc_get_min_lsn('dbo_mytable') AS 清理后最小LSN

如果返回的最小LSN和你清理时传入的@max_lsn一致，说明老数据已经全部清理完成，剩余的记录为合法保留/新增的记录。

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