CDC在sql server 2017中无法使用的问题

最新推荐文章于 2025-03-20 14:12:39 发布

bill_live

最新推荐文章于 2025-03-20 14:12:39 发布

阅读量391

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/stswordman/p/8131278.html

本文介绍了一个关于SQL Server中的Change Data Capture (CDC)功能出现的错误：当执行sp_MScdc_capture_job时引发的错误消息及解决方法。产品组已确认此为一个Bug，并在后续的累积更新中修复。文中提供了详细的规避措施步骤。

Symptom

===

sp_MScdc_capture_job in the CDC job raised error message

Msg 217, Level 16, State 1, Procedure msdb.dbo.sp_cdc_sqlagent_log_jobhistory, Line 19 [Batch Start Line 12] Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

产品组已经确认这是一个bug，会在后续的cu中解决。

workaround

===

1) Stop and disable CDC capture job. Make sure the job is not invoked till the fix is out.(If it runs again, CDC will break)

2) Use below query to identify the job_id for the capture job. Note – We need to execute below query for every DB that has CDC enabled –

select c.job_id, s.run_date, s.run_time from msdb.dbo.cdc_jobs c join msdb.dbo.sysjobhistory s on c.job_id = s.job_id where c.database_id = db_id() and c.job_type = N'capture' and s.step_id = 1

3) Use query similar to below to delete the record from msdb.dbo.sysjobhistory for capture job’s step id 1

delete from msdb.dbo.sysjobhistory where job_id = 'job_id that we got from #2' and step_id = 1

Note – Please be careful/cautious while doing this step. If required then please wrap this under a transaction to avoid affecting other rows.

4) After this you can either run sys.sp_MScdc_capture_job manually for every CDC enabled DB or create another job that executes sys.sp_MScdc_capture_job for every CDC enabled DB

=====2018.3.27====

该问题已经已经在sql 2007 cu5中解决

转载于:https://www.cnblogs.com/stswordman/p/8131278.html