CDC作业历史记录无法删除问题

原创 z_cloud_for_SQL 2024-02-27

背景

数据库开启CDC功能后，每天会生成大量的历史记录，即使达到参数“每个作业的最大历史记录“的阈值后也不会被删除，导致其它作业的历史记录被删除，无法查看以前的执行情况，非常不方便。

现象

数据库开启CDC后会创建“capture”和“cleanup”两个作业，capture作业会生成大量的历史记录，而且参数“每个作业的最大历史记录”不起作用，一直不删除。

$C:\Users\weijunbo\Documents\WeChat Files\wxid_8ula1j9qm27222\FileStorage\Temp\e09f46885e6b7f0c8c7a827d38f87d3d.png$

$C:\Users\weijunbo\Documents\WeChat Files\wxid_8ula1j9qm27222\FileStorage\Temp\a3bc7670656f559f28b234eb6b06ec6f.png$

分析

首先通过跟踪定位到SQL Agent服务在执行作业时调用存储过程msdb.dbo. sp_sqlagent_log_jobhistory处理作业历史记录。查看该存储过程的定义，首先把当前运行数据插入到历史记录表中。

$C:\Users\weijunbo\Documents\WeChat Files\wxid_8ula1j9qm27222\FileStorage\Temp\af1019951048d24b0b1777e32a6d5180.png$

然后通过@step_id判断是整个作业完成的记录还是执行过程中步骤的记录，如果是作业已经完成的记录，执行存储过程msdb.dbo.sp_jobhistory_row_limiter对历史记录进行清理。

$C:\Users\weijunbo\Documents\WeChat Files\wxid_8ula1j9qm27222\FileStorage\Temp\485447bfdddcc606d126a0bc95dd1f9d.png$

查看存储过程msdb.dbo.sp_jobhistory_row_limiter的定义，首先从注册表中获取参数“所有作业最大行数”和“每个作业最大行数”的值。

$C:\Users\weijunbo\Documents\WeChat Files\wxid_8ula1j9qm27222\FileStorage\Temp\f71ede2e0aece25c49d0f0407f326a75.png$

$C:\Users\weijunbo\Documents\WeChat Files\wxid_8ula1j9qm27222\FileStorage\Temp\1012f353b35bdc2df4656911e4946e9a.png$

先根据参数“每个作业最大行数”判断当前作业是否超过限制，并删除旧的记录。
$C:\Users\weijunbo\Documents\WeChat Files\wxid_8ula1j9qm27222\FileStorage\Temp\b87eed020d4e7c9c102d2d45f25c9f01.png$

然后根据参数“所有作业最大行数”判断所有作业历史记录是否超过限制，并删除旧的记录。

$C:\Users\weijunbo\Documents\WeChat Files\wxid_8ula1j9qm27222\FileStorage\Temp\6ca6f60c120817003a4cb774fdb8f22d.png$

接下来分析为什么CDC的历史记录无法删除，通过跟踪定位到捕获作业调用存储过程[sys].[sp_cdc_scan]在一个没有退出方式的循环中扫描事务日志，并调用存储过程msdb.dbo. sp_sqlagent_log_jobhistory 记录历史记录，因为传递的@step_id的值代表这是执行步骤的记录，不是作业完成的记录，因此不会触发清理日志的存储过程，导致历史记录越积累越多。

$C:\Users\weijunbo\Documents\WeChat Files\wxid_8ula1j9qm27222\FileStorage\Temp\da60f715303258e837f1cb5924e896be.png$

总结：

capture作业一直是运行的，所以记录的都是作业步骤的日志，不会执行到清理历史记录的步骤，导致历史记录越积累越多；
其它作业执行完成清理历史记录时，因为capture作业的历史记录越积累越多，达到“所有作业最大行数”的阈值，删除所有作业旧的历史记录。capture作业运行频繁，所以保留的大都是capture作业的历史记录，其它运行不频繁的作业的历史记录被删除。

解决

方法一：创建一个新的作业，定期删除CDC的作业历史记录。

CREATE PROC [dbo].[usp_gt_delete_cdc_job_history]ASBEGIN DECLARE @job_id UNIQUEIDENTIFIER;
DECLARE cdc_job_cursor CURSOR FOR SELECT job_id FROM msdb.dbo.cdc_jobs WHERE job_type = 'capture';
OPEN cdc_job_cursor; FETCH NEXT FROM cdc_job_cursor INTO @job_id; WHILE @@FETCH_STATUS = 0 BEGIN EXECUTE msdb.dbo.sp_jobhistory_row_limiter @job_id;
FETCH NEXT FROM cdc_job_cursor INTO @job_id; END
CLOSE cdc_job_cursor; DEALLOCATE cdc_job_cursor;END

方法二：到msdb中修改存储过程的定义，增加对CDC作业处理的逻辑。该方式已经反馈给微软，在SQL Server下一个版本会采用，也算对SQL Server做一点小贡献。

$C:\Users\weijunbo\Documents\WeChat Files\wxid_8ula1j9qm27222\FileStorage\Temp\f341995d94565c123cafa64580a43602.png$