The Database Failed Over. Its CDC Jobs Didn't.

Enable Change Data Capture on a database in an Availability Group, fail the group over, and CDC stops working. Not loudly. Nothing errors, nothing alerts, and the database is up on the new primary with every cdc.* table exactly where it was. It just quietly stops gaining rows.

The reason is a boundary that is easy to forget once everything is running: an Availability Group fails over databases. The capture and cleanup processes are SQL Agent jobs, Agent jobs are rows in msdb, and msdb is a system database on each instance. It is not in the group, so it does not move.

This is a write-up of how I closed that gap in 2018, on a multi-node Always On estate. The pattern has not aged — the boundary it works around is structural, not a version bug — so the procedure is here as it shipped, with a section at the end on what I would write differently today.

What an Availability Group actually moves§

The group carries the user databases and everything inside them. For CDC that is the entire captured state: the cdc schema, the change tables, the capture instances, the LSN-to-time mapping, the configuration.

What it does not carry is anything instance-scoped. Logins. Linked servers. Credentials. Agent jobs.

Those two jobs are not interchangeable, and they fail differently:

CAPTURE JOB

continuous

CLEANUP JOB

scheduled

DEFAULT RETENTION

72 hours

The capture job runs continuously — it starts, and then it stays in the log reading committed changes into the change tables until something stops it. The cleanup job runs on a schedule and deletes change rows older than the retention window, 4320 minutes by default.

So the capture job has to exist and be running. The cleanup job only has to exist.

Five things that make this awkward§

Working through it in 2018, these were the constraints that ruled out the obvious answers:

There can be more than one Availability Group, and their primaries can sit on different nodes. “Fix it on the primary” is not a single place.

Enabling CDC creates the jobs only on the node you enabled it on. Fail over to a node that has never hosted this database and there are no jobs at all to start.

Fail back to a node that hosted it before and the jobs are still there — left behind, and in a failed state, because they were running against a database that stopped being primary underneath them. Existing is not the same as working.

The capture job has to be started after every failover. Automatic failovers included, which is to say the ones nobody is watching.

The cleanup job only has to be created. Its schedule survives; it is the easy half.

Together these rule out a one-off fix on one node, and they rule out anything that assumes a known starting state. Whatever runs has to work out where it is and what is missing, every time.

1. 01 FAILOVER

   The Availability Group moves the databases to another replica — automatically, or during a patching round.

   ↓

2. 02 WHAT MOVES

   The user databases, and with them every cdc schema, change table and capture instance.

   ↓

3. 03 WHAT DOES NOT

   msdb, and so the capture and cleanup Agent jobs. They are instance-scoped.

   ↓

4. 04 CONSEQUENCE

   Nothing on the new primary is reading the log into the change tables.

   ↓

5. 05 SYMPTOM OBSERVED

   Change tables stop gaining rows. Nothing errors. The database is up and healthy.

What a stopped capture job costs§

The failure is quiet, but it is not free, and the cost arrives in three stages rather than all at once.

Immediately: a gap downstream. Anything reading cdc.<instance>_CT on a schedule sees no new rows. Whether that is a delay or a permanent hole depends entirely on how the consumer tracks its position — a job that moves its watermark forward on an empty read will step straight over the window it missed.

Then: the log stops truncating. A CDC-enabled database holds the log truncation point at the last LSN the capture process has read. With no capture process, that point stops advancing, and log backups will not free the space — log_reuse_wait_desc reports REPLICATION and the file grows. This is the part that eventually gets noticed, usually as a disk alert rather than as a CDC problem.

Eventually: the retention window catches up. Cleanup deletes change rows whose commit time falls outside the retention period. Restart capture after an outage longer than that window and the changes it finally writes are already older than retention — eligible for deletion by the next cleanup pass that runs.

That third one is a mechanism, not something I measured on this estate: I never let an outage run long enough to watch it happen. It is the reason worth internalising anyway, because it is what turns “CDC is behind” into “those changes are gone”, and it is why the honest way to think about a stopped capture job is as a clock running against the retention window.

The original framing of this problem was “we will remember to restart the jobs after a failover.” We would not, and neither would anyone else at 2 a.m. after an automatic failover. That is what makes this an automation problem rather than a runbook entry.

The watchdog§

The design that falls out of the constraints is a single procedure, scheduled identically on every node in the setup, that works out for itself whether it has anything to do.

CREATE PROCEDURE sp_cdc_hadr_watchdog
AS
BEGIN

SET NOCOUNT ON

DECLARE @database_name NVARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)

DECLARE cdc_watchdog_dbs CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT  D.name
FROM    sys.dm_hadr_availability_replica_states AS A
        JOIN sys.availability_replicas AS B ON B.replica_id = A.replica_id
        JOIN sys.availability_groups AS AG ON AG.group_id = A.group_id
        JOIN sys.availability_databases_cluster AS ADC ON ADC.group_id = A.group_id
        JOIN sys.databases AS D ON D.name = ADC.database_name AND D.is_cdc_enabled = 1
WHERE B.replica_server_name = @@SERVERNAME AND A.role_desc = 'PRIMARY'

OPEN cdc_watchdog_dbs

FETCH NEXT FROM cdc_watchdog_dbs INTO @database_name

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = '
USE ['+@database_name+'];

IF OBJECT_ID(''tempdb..#xp_results'') IS NOT NULL
EXEC (''DROP TABLE #xp_results'')

IF OBJECT_ID(''tempdb..#CDCJobs'') IS NOT NULL
EXEC (''DROP TABLE #CDCJobs'')

CREATE TABLE #CDCJobs(job_id          UNIQUEIDENTIFIER
                    , job_type        CHAR(8)
                    , job_name        NVARCHAR(255)
                    , maxtrans        SMALLINT
                    , maxscans        SMALLINT
                    , continuous      BIT
                    , pollinginterval SMALLINT
                    , retention       SMALLINT
                    , threshold       SMALLINT)

INSERT INTO #CDCJobs
EXEC sys.sp_cdc_help_jobs

DECLARE @job_id_capture UNIQUEIDENTIFIER
DECLARE @job_owner_capture sysname

DECLARE @job_id_cleanup UNIQUEIDENTIFIER

DECLARE @is_running INT

SELECT @job_owner_capture = SP.name, @job_id_capture = S.job_id
FROM #CDCJobs AS CJ
JOIN msdb.dbo.sysjobs AS S ON s.name = CJ.job_name
JOIN sys.server_principals AS SP ON SP.sid = S.owner_sid
WHERE CJ.job_type = ''capture''

SELECT @job_id_cleanup = CJ.job_id
FROM #CDCJobs AS CJ
WHERE CJ.job_type = ''cleanup''

IF @job_id_capture IS NULL
 EXEC sys.sp_cdc_add_job ''capture''

IF @job_id_cleanup IS NULL
 EXEC sys.sp_cdc_add_job ''cleanup''

IF @job_id_capture IS NOT NULL
 BEGIN
  CREATE TABLE #xp_results (job_id             UNIQUEIDENTIFIER NOT NULL,
        last_run_date         INT              NOT NULL,
        last_run_time         INT              NOT NULL,
        next_run_date         INT              NOT NULL,
        next_run_time         INT              NOT NULL,
        next_run_schedule_id  INT              NOT NULL,
        requested_to_run      INT              NOT NULL,
        request_source        INT              NOT NULL,
        request_source_id     sysname          COLLATE database_default NULL,
        running               INT              NOT NULL,
        current_step          INT              NOT NULL,
        current_retry_attempt INT              NOT NULL,
        job_state             INT              NOT NULL)

  INSERT INTO #xp_results
  EXEC master.dbo.xp_sqlagent_enum_jobs  0, @job_owner_capture, @job_id_capture

  SELECT @is_running = XR.running
  FROM #xp_results AS XR

  IF @is_running = 1
   RETURN
  ELSE
   EXEC msdb.dbo.sp_start_job @job_id = @job_id_capture

 END'

EXEC (@SQL)

    FETCH NEXT FROM cdc_watchdog_dbs INTO @database_name
END

CLOSE cdc_watchdog_dbs
DEALLOCATE cdc_watchdog_dbs

END

How it works§

The cursor is the whole design decision. Its query joins the replica states, the availability groups and the clustered database list against sys.databases, filtered to is_cdc_enabled = 1 and to replicas where this server is PRIMARY. On a secondary it returns an empty set, the loop body never runs, and the procedure costs a few milliseconds.

For each database that comes back, the body runs as dynamic SQL, and it has to. sys.sp_cdc_help_jobs and sys.sp_cdc_add_job are database-scoped — they report on and act against the current database — so the batch opens with USE [<database>] and everything else follows from there.

Inside that batch:

1. sys.sp_cdc_help_jobs fills a temp table with whatever CDC jobs this database currently has, and the job id and owner are joined back through msdb.dbo.sysjobs.
2. Any job that came back NULL is created with sys.sp_cdc_add_job — 'capture', 'cleanup', or both. This covers the never-hosted-here case.
3. If a capture job exists, its live state is read, and if it is not already running it is started with sp_start_job. This covers the failed-back case, where the job exists but is sitting in a failed state.

The two halves matter separately: step 2 handles a node that has never seen the database, step 3 handles a node that has. Between them there is no starting state the procedure cannot recover from, which is exactly what “runs on every node, on a schedule” requires.

One detail that reads wrong at first glance: the RETURN when the job is already running exits the dynamic batch, not the outer procedure. Control comes back to EXEC (@SQL) and the cursor moves on to the next database. That is the behaviour you want, but it is not what the keyword looks like it is doing.

The one dependency I was not happy about§

To decide whether the capture job is already running, the procedure calls master.dbo.xp_sqlagent_enum_jobs and reads its running column.

I called it out as a risk when I wrote this, and it is the first thing I would replace now — see below. Worth being clear about the exposure: the failure mode is an INSERT ... EXEC that stops matching the table definition, which fails loudly at the next run rather than silently doing the wrong thing. That is the good kind of fragile, but it is still fragile.

Deploying it§

Three things, and the order matters less than the completeness:

• Create the procedure on every replica. It is instance-scoped work, so it belongs somewhere instance-scoped — master, or whichever utility database the estate already has on every node.
• Schedule it on every replica, on the same schedule. The interval is the worst-case gap between a failover and CDC resuming, so it wants to be short — minutes, not hours. The procedure is close to free on a secondary.
• Give the Agent account rights to sp_start_job and to sys.sp_cdc_add_job in the CDC-enabled databases. This is the step that gets missed, and it fails at the moment you least want it to.

What I would change today§

The design holds. The implementation has three things I would not write the same way.

Replace the undocumented call. There are two supported ways to ask whether a job is running, and only one of them is an improvement.

msdb.dbo.sp_help_job with @job_aspect = N'JOB' returns current_execution_status, where 1 means executing. But reading it still means INSERT ... EXEC into a table declared to match the procedure’s full result set — the same brittle coupling as before, just to a documented shape instead of an undocumented one.

msdb.dbo.sysjobactivity is a table, so it answers the same question in a plain SELECT with no result shape to keep in sync:

SELECT @is_running = CASE
         WHEN JA.start_execution_date IS NOT NULL
          AND JA.stop_execution_date IS NULL THEN 1
         ELSE 0
       END
FROM   msdb.dbo.sysjobactivity AS JA
WHERE  JA.job_id = @job_id_capture
       AND JA.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions);

The session filter is the part that is easy to miss: sysjobactivity keeps a row per Agent session, so without it an old session’s row can answer for the current one.

Use sys.fn_hadr_is_primary_replica for the role test. The four-way join in the cursor is doing by hand what one supported function does per database:

SELECT name
FROM   sys.databases
WHERE  is_cdc_enabled = 1
       AND sys.fn_hadr_is_primary_replica(name) = 1;

Wrap each database in its own error handling, and quote the name. As written, a failure on one database — a permission problem, a job that will not start — aborts the cursor and leaves the databases after it in the loop unchecked. A TRY/CATCH per iteration keeps one bad database from hiding the rest. And QUOTENAME(@database_name) in the USE rather than plain concatenation, not because the name is untrusted here, but because it is the one-word habit that never costs anything.

The larger thing I would add is a signal. This procedure fixes the problem silently, which is right, but it means nobody ever learns how often it fires. A row written on every corrective action — job created, job started, which database, which node — turns an invisible safety net into something you can point at during a post-incident review, and tells you whether a failover ever left CDC down for longer than the schedule interval.

Key takeaways§

Availability Groups fail over databases, not instances. Agent jobs, logins, linked servers and credentials all sit outside the group. Anything that depends on them needs its own answer, and CDC is the case where the absence is completely silent.

“We will remember” is not a plan for something that happens automatically at 2 a.m. If the recovery step is only ever needed after an unattended event, it has to be unattended too.

Make the automation decide where it is rather than being told. The version of this that runs only on the primary needs its schedule moved at every failover — the same manual step, one level up. Running everywhere and asking the instance which role it holds removes the step instead of relocating it.

A quiet failure is worse than a loud one. Nothing about a stopped capture job announces itself until the log fills or somebody downstream notices data that stopped arriving days ago. That is the argument for the watchdog, and it is also the argument for having it say when it acted.