app软件开发网站,营销型企业网站建设案例,网站版面设计注意事项,门头设计网站推荐背景 数据库开启CDC功能后#xff0c;每天会生成大量的历史记录#xff0c;即使达到参数“每个作业的最大历史记录“的阈值后也不会被删除#xff0c;导致其它作业的历史记录被删除#xff0c;无法查看以前的执行情况#xff0c;非常不方便。 现象
数据库开启CDC后会创建…背景
数据库开启CDC功能后每天会生成大量的历史记录即使达到参数“每个作业的最大历史记录“的阈值后也不会被删除导致其它作业的历史记录被删除无法查看以前的执行情况非常不方便。 现象
数据库开启CDC后会创建“capture”和“cleanup”两个作业capture作业会生成大量的历史记录而且参数“每个作业的最大历史记录”不起作用一直不删除。 分析
首先通过跟踪定位到SQL Agent服务在执行作业时调用存储过程msdb.dbo. sp_sqlagent_log_jobhistory处理作业历史记录。
查看该存储过程的定义首先把当前运行数据插入到历史记录表中。 然后通过step_id判断是整个作业完成的记录还是执行过程中步骤的记录如果是作业已经完成的记录执行存储过程msdb.dbo.sp_jobhistory_row_limiter对历史记录进行清理。 查看存储过程msdb.dbo.sp_jobhistory_row_limiter的定义首先从注册表中获取参数“所有作业最大行数”和“每个作业最大行数”的值。 先根据参数“每个作业最大行数”判断当前作业是否超过限制并删除旧的记录。 然后根据参数“所有作业最大行数”判断所有作业历史记录是否超过限制并删除旧的记录。 接下来分析为什么CDC的历史记录无法删除通过跟踪定位到捕获作业调用存储过程[sys].[sp_cdc_scan]在一个没有退出方式的循环中扫描事务日志并调用存储过程msdb.dbo. sp_sqlagent_log_jobhistory 记录历史记录因为传递的step_id的值代表这是执行步骤的记录不是作业完成的记录因此不会触发清理日志的存储过程导致历史记录越积累越多。 总结 capture作业一直是运行的所以记录的都是作业步骤的日志不会执行到清理历史记录的步骤导致历史记录越积累越多 其它作业执行完成清理历史记录时因为capture作业的历史记录越积累越多达到“所有作业最大行数”的阈值删除所有作业旧的历史记录。capture作业运行频繁所以保留的大都是capture作业的历史记录其它运行不频繁的作业的历史记录被删除。
解决
方法一创建一个新的作业定期删除CDC的作业历史记录。
CREATE PROC [dbo].[usp_gt_delete_cdc_job_history]
AS
BEGINDECLARE job_id UNIQUEIDENTIFIER;DECLARE cdc_job_cursor CURSOR FOR SELECTjob_idFROMmsdb.dbo.cdc_jobsWHEREjob_type capture;OPEN cdc_job_cursor;FETCH NEXT FROM cdc_job_cursor INTO job_id;WHILE FETCH_STATUS 0BEGINEXECUTE msdb.dbo.sp_jobhistory_row_limiter job_id;FETCH NEXT FROM cdc_job_cursor INTO job_id;ENDCLOSE cdc_job_cursor;DEALLOCATE cdc_job_cursor;
END方法二到msdb中修改存储过程的定义增加对CDC作业处理的逻辑。该方式已经反馈给微软在SQL Server下一个版本会采用也算对SQL Server做一点小贡献。 以下是具体的代码。
ELSE
BEGINIF EXISTS(SELECT 1 FROM msdb.sys.tables WHERE schema_id SCHEMA_ID(dbo) AND name cdc_jobs)BEGINIF EXISTS(SELECT 1 FROM msdb.dbo.cdc_jobs WHERE job_id job_id AND job_type capture)BEGINEXECUTE msdb.dbo.sp_jobhistory_row_limiter job_idENDEND
END产品服务
SQL专家云数据库智能运维平台
Moebius数据库多活集群
数据库协作运维服务 北京格瑞趋势科技有限公司是聚焦于数据服务的高新技术企业成立于2008年创始团队及核心技术人员来自微软和雅虎。微软数据平台金牌合作伙伴。通过产品服务双轮驱动的业务模式15年间累计服务4000客户覆盖互联网、市政、交通、电信、医疗、教育、电力、制造业等各个领域。
