WordsOnTech

CDC job fails: Invalid object name ‘msdb.dbo.cdc_jobs’. [SQLSTATE 42S02] (Error 208)

Issue: In an always on setup, CDC jobs should be created on all the replicas of always on availability group. Sometime you may find that CDC capture and cleanup job is running successful on some of replicas but failing on other replicas with below error:

Error message:
Executed as user: Domain\user. Invalid object name ‘msdb.dbo.cdc_jobs’. [SQLSTATE 42S02] (Error 208)  The call to sp_MScdc_capture_job by the Capture Job for database ‘DbName’ failed. Look at previous errors for the cause of the failure. [SQLSTATE 42000] (Error 22864)

Cause: This issue occurs if CDC capture job has been created on primary replica by using stored procedure sp_cdc_add_job while created on other replicas by scripting out the job from primary replica and running script on other replicas.
Stored procedure sp_cdc_add_job creates some tables and structures as part of creation of CDC jobs which does not gets created when we create CDC jobs on secondary replicas by scripting out the job from primary replica and running script on secondary replicas.

Fix: In order to fix this, we need to follow below steps:

  1. Delete existing CDC capture and cleanup jobs
  2. Make secondary database as primary by failing over the AG to current secondary replica and create CDC capture and cleanup job using below script
    Use <DatabaseName>
    GO
    EXEC sys.sp_cdc_add_job @job_type = ‘capture’;
    EXEC sys.sp_cdc_add_job @job_type = ‘cleanup’;

Hope this information helps!!
Happy learning!!

Exit mobile version