随笔-209  评论-504  文章-2 

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

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中解决

posted on 2017-12-28 02:47 stswordman 阅读(...) 评论(...) 编辑 收藏