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).
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
该问题已经已经在sql 2007 cu5中解决