手动执行sys.sp_cdc_scan后Azure SQL CDC自动捕获任务异常停止

• 检查CDC捕获作业状态
  手动执行sys.sp_cdc_scan可能干扰自动捕获作业的执行上下文，先确认作业是否正常启用：

  SELECT name, is_enabled, last_run_date, last_run_time, last_run_outcome
  FROM msdb.dbo.sysjobs
  WHERE name LIKE 'cdc.%_capture';
  

  若作业被禁用或执行结果异常，查看作业历史记录定位问题：

  SELECT job_id, step_id, message, run_date, run_time
  FROM msdb.dbo.sysjobhistory
  WHERE job_id = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name LIKE 'cdc.%_capture');
  

• 排查锁资源冲突
  sys.sp_cdc_scan是单线程操作，手动调用时可能持有CDC系统资源的锁，导致自动捕获任务无法获取资源而挂起。检查当前锁状态：

  SELECT request_session_id, resource_type, request_mode, request_status
  FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID()
    AND resource_type IN ('OBJECT', 'PAGE')
    AND OBJECT_NAME(resource_associated_entity_id) LIKE 'cdc.%';
  

• 验证自动捕获进程运行状态
  Azure SQL的CDC自动捕获由SQL代理作业驱动，手动执行sys.sp_cdc_scan可能终止自动进程的会话。检查是否存在捕获进程会话：

  SELECT session_id, program_name, status, command
  FROM sys.dm_exec_sessions
  WHERE program_name LIKE 'SQLAgent - CDC Capture Job%';
  

  若没有对应会话，执行以下命令重启捕获作业：

  EXEC sys.sp_cdc_enable_job @job_type = 'capture';
  

• 检查CDC配置参数
  手动调用sys.sp_cdc_scan可能意外修改CDC配置，确认自动扫描作业是否处于启用状态：

  SELECT is_cdc_enabled, scan_job_enabled, scan_job_id
  FROM sys.databases
  WHERE name = DB_NAME();
  

  如果scan_job_enabled为0，执行EXEC sys.sp_cdc_enable_job @job_type = 'capture';重新启用。

• 排查DDL触发器的副作用
  你的存储过程由DDL触发器调用，需确认触发器逻辑是否在执行sys.sp_cdc_scan时，无意中终止了自动捕获作业或修改了其启用状态。检查触发器定义，排查是否存在影响SQL代理作业的操作。

• 分析事务日志读取位置
  手动执行sys.sp_cdc_scan可能将日志读取位置推进到超出自动作业预期的范围，导致自动作业无法识别新变更。对比CDC捕获的LSN和日志最新LSN：

  -- CDC已捕获的最新LSN
  SELECT TOP 1 end_lsn FROM cdc.lsn_time_mapping ORDER BY tran_end_time DESC;
  -- 事务日志最新LSN
  SELECT MAX(CAST([Current LSN] AS VARCHAR(50))) FROM sys.fn_dblog(NULL, NULL);
  

  若两者一致但自动作业未捕获新变更，需检查作业的唤醒机制是否正常。

补充：事务内调用sys.sp_cdc_scan虽能立即获取变更，但会破坏自动捕获的正常运行逻辑。若需实时获取变更，建议在事务提交后调用该存储过程，而非事务内部。

内容的提问来源于stack exchange，提问作者Abhinav Singh