# SQL Server & MySQL CDC: Positions, Retention and What Breaks

Page title: SQL Server & MySQL CDC Specifics

> Positions, retention and failure modes for Debezium on MySQL and SQL Server: binlog purge, GTIDs, CDC cleanup jobs, LSNs, triage queries and runbooks.

Canonical page: <https://sandgraal.github.io/letstalkcdc/sql-server-mysql-cdc/>

The Postgres slot runbook, for the other two engines. Where the log position lives in MySQL and SQL Server, how long it stays readable, what happens when it is purged, and what the sink must do with the duplicates a restart brings.

See the positions MySQL runbook SQL Server runbook

## What this page covers

You run a Debezium connector against MySQL or SQL Server and want to know where the log position lives, how long it stays readable, what breaks when it is lost, and what the sink must do about the duplicates a restart brings. The Postgres version of this page is [Postgres slots & WAL growth](https://sandgraal.github.io/letstalkcdc/postgres-replication-slots/); the setup steps are in the [MySQL](https://sandgraal.github.io/letstalkcdc/quickstarts/quickstart-mysql/) and [SQL Server](https://sandgraal.github.io/letstalkcdc/quickstarts/quickstart-mssql/) quickstarts, and [Oracle](https://sandgraal.github.io/letstalkcdc/oracle-notes/) has its own notes.

The thread is the same as everywhere on this site. A connector resumes from a stored *log position*. Offsets are committed periodically, so a restart re-sends some changes: delivery is at-least-once. If the position can no longer be read, the only correct way back is a re-snapshot into a sink that is idempotent: keyed by primary key and ordered by log position, never by a timestamp. There is no end-to-end exactly-once anywhere on this page.

The two engines differ in *who holds the position*, and that decides which failure you get. Postgres keeps a server-side slot that pins WAL until the consumer confirms it. **MySQL keeps nothing for a consumer that is not connected**: the binlog is purged on a timer. **SQL Server's connector does not read the log at all**: it reads change tables that a capture job fills and a cleanup job empties, so there are two clocks (and two jobs) between a commit and your connector.

> **Checked against** (read on 2026-10-09): the Debezium reference at `debezium.io/documentation/reference/stable` for the MySQL and SQL Server connectors, which was labelled 3.7; the MySQL 8.4 Reference Manual, plus the 8.0 manual for `expire_logs_days`; and Microsoft Learn pages for SQL Server change data capture. A few statements come from the Debezium source code on its `main` branch, not a release tag; they are marked. Where I could not confirm something, the text says “check your version”. The last section lists what was run and what was not.

## The log position, engine by engine

Every Debezium change event carries a `source` block with the position of the change in the database's log. That block is what a sink should compare. The fields differ by engine:

| Engine | Fields in `source` | What the sink compares | Where it stops being comparable |
| --- | --- | --- | --- |
| MySQL | `file`, `pos`, `row`, `gtid`, `server_id` | The tuple (binlog file number, `pos`, `row`). Debezium describes `row` as the “row within the event”, so one binlog event that carries several rows has one `file`/`pos` and several `row` values. The `gtid` names a whole transaction. | A different server (file and position are that server's own coordinates), or a reset of the binlog: `RESET BINARY LOGS AND GTIDS` restarts the file number at `.000001`, whereas `PURGE BINARY LOGS` does not reset numbering. |
| SQL Server | `commit_lsn`, `change_lsn`, `event_serial_no` | The tuple (`commit_lsn`, `change_lsn`, `event_serial_no`). `commit_lsn` alone is shared by every change in a transaction. Snapshot events have a `commit_lsn` and null `change_lsn` and `event_serial_no`. | A different database that is not a continuation of the same log (a restore into a new database, for example). My understanding, not a sentence I found in the docs: replicas in one availability group share one log lineage. |

The tuple is the same one the [Partitioning page](https://sandgraal.github.io/letstalkcdc/partitioning/#recon) lists for the version guard. Compare it as a typed tuple or as fixed-width text, never as free text, and never substitute `ts_ms` or a Kafka offset. Glossary: [LSN / SCN](https://sandgraal.github.io/letstalkcdc/glossary/#lsn-scn), [log retention](https://sandgraal.github.io/letstalkcdc/glossary/#log-retention), [at-least-once](https://sandgraal.github.io/letstalkcdc/glossary/#at-least-once).

### What a SQL Server LSN looks like

An LSN is 10 bytes. Microsoft documents its structure as `[VLF ID:Log Block ID:Log Record ID]` and gives the example `00000031:00000da0:0001`, where `0x31` is the virtual log file ID, `0xda0` the log block ID and `0x1` the first record in that block. Debezium writes it in the same shape, as hex groups of 4 bytes, 4 bytes and 2 bytes (`00000027:00000758:0003` in its docs), and the SQL Server functions return it as `binary(10)`. So the string is eight, eight and four hex digits, and strings of that fixed width compare correctly as text only if they use the same case; Debezium's own code prints lower case.

How the three event fields map to CDC's own columns is not spelled out in the Debezium reference. In the connector source on `main`, `commit_lsn` comes from `__$start_lsn` and `change_lsn` from `__$seqval`; Microsoft describes `__$start_lsn` as the commit LSN that orders transactions and `__$seqval` as the column that orders changes inside one transaction. Debezium's reference says `event_serial_no` “differentiates events that carry the same position”: an update is stored as two rows (before and after) and Debezium emits one event with `event_serial_no = 2`; a primary-key update is a delete plus a create that share both LSNs and get `1` and `2`.

> **Check your version.** The connector's `data.query.mode` is `direct` by default (it reads the change tables itself, not through the `fn_cdc_get_all_changes` functions). Debezium's reference says that in direct mode a position also includes `__$command_id`, which is not in the `source` block, and the source orders by `__$start_lsn`, then `__$command_id`, then `__$seqval`. I did not verify whether the command id can ever disagree with the change LSN within one transaction. A comment in `SqlServerConnection.java` on `main` says every command id observed so far maps to exactly one `__$seqval`, but that this is not documented. The tuple above is what the site's version guard uses; if you can reproduce a disagreement on your version, treat the position as unique-per-change but not strictly ordered inside a transaction.

### What a MySQL position looks like

MySQL gives you two coordinate systems. **File and position** (`mysql-bin.000003`, `570`) are local to one server. A **GTID** is `source_id:transaction_id`, for example `3E11FA47-71CA-11E1-9E33-C80AA9429562:23`; the `transaction_id` is a sequence number assigned in commit order on the originating server, and GTIDs are unique across a replication topology. A GTID *set* such as `3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5` is what `gtid_executed` and `gtid_purged` hold. A set is not a single number you can sort rows by, and numbers from two different `source_id`s are not comparable.

For the connector, GTIDs are what survive a move to another server in the same cluster (see replicas and failover). For the sink guard, the file number, position and row index are what order the rows of one server's log. Use the GTID to name a transaction, not to order it.

## MySQL: binlog settings, retention and purge

### Settings the connector needs

| Setting | What it must be, and why |
| --- | --- |
| `binlog_format` | `ROW`. Debezium's table says it must be set to `ROW`. The MySQL 8.4 manual says the default is `ROW`, binary logging is on by default, and the variable itself is deprecated, so only row-based logging should be used for new setups. |
| `binlog_row_image` | `FULL` (the MySQL default). With `MINIMAL` the after-image of an update holds only the columns the statement set, so the event cannot describe the whole row. Debezium's table says `FULL`. The quickstart and troubleshooting pages say “FULL or MINIMAL per connector support”; for Debezium, use `FULL`. |
| `gtid_mode`, `enforce_gtid_consistency` | `ON` for both. Debezium: GTIDs are not required, but they simplify replication and are what lets a connector move to another server. Required for multi-primary topologies. |
| `server_id` (connector: `database.server.id`) | The connector joins the cluster as another replica, so its `database.server.id` must be unique across everything running in the cluster. MySQL: pick an integer from 1 to 232&minus;1 that no other source or replica uses. |
| `binlog_expire_logs_seconds` | How long binlog files live. Default `2592000` (30 days). See below; this is the setting that decides how long a stopped connector is safe. |
| Privileges | `REPLICATION SLAVE` to read the binlog, `REPLICATION CLIENT` for the status statements; the snapshot also needs `SELECT`, `RELOAD` and `SHOW DATABASES`, and `LOCK TABLES` where global read locks are not allowed (Amazon RDS and Aurora, per Debezium). On RDS, Debezium notes that automated backups must be enabled or the binlog is disabled. |

### Retention: nothing holds the files for you

MySQL removes binlog files when they pass the expiration period. Removal can happen at startup and when the binary log is flushed. The period is `binlog_expire_logs_seconds`, default 30 days; `binlog_expire_logs_auto_purge` (MySQL 8.0.29 and later) turns automatic purging off, and a seconds value of `0` also stops it. The older `expire_logs_days` is deprecated in 8.0 and was removed in 8.4, where setting it is an error. Managed services have their own retention controls: check your provider.

The crucial sentence is in the manual's `PURGE BINARY LOGS` page. Purging is safe while replicas are replicating, and a file a *connected* replica is reading is not deleted. But “if a replica is not connected and you happen to purge one of the log files it has yet to read, the replica cannot replicate after it reconnects.” A stopped Debezium connector is exactly that replica. The manual's advice is to set an expiration period no lower than the longest your replicas might lag; for a CDC connector, make it longer than the longest outage plus snapshot time you intend to survive, and watch the disk that retention costs.

### What happens when the position was purged

- **File and position.** The server refuses to serve the file. MySQL reports error 1236 (`ER_SOURCE_FATAL_ERROR_READING_BINLOG`); the text after the colon varies and I did not verify it.
- **GTID auto-positioning.** The server sends error 1789, `ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS`: “Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period.” The manual adds that the replica cannot recover automatically, and that reconnecting *without* auto-positioning “only results in the loss of the purged transactions on the replica”: the gap is skipped, silently.
- **Debezium.** The snapshot reference says that if the logs have been pruned the connector fails with an error saying a new snapshot is required, and that `snapshot.mode=when_needed` makes it snapshot automatically when a recorded offset names a binlog position or GTID the server no longer has. The default is `initial`, which snapshots only when there is no offset (or an earlier snapshot did not finish). The “Behavior when things go wrong” section words the same situation differently (it says the connector performs another initial snapshot, and fails only if snapshots are disabled), so check which happens on your version before you rely on either.

> **Do not “fix” a purged position by moving past it.** Debezium's `set-binlog-position` signal can start the connector at a later position, and its docs say the connector then skips the change events in between and does not capture or stream them. That is the same silent gap MySQL warns about for replicas. The recovery is a re-snapshot into the idempotent sink, then a reconcile: a snapshot copies the rows that exist now, so a row deleted during the gap stays in a sink you did not rebuild. See [Snapshotting](https://sandgraal.github.io/letstalkcdc/snapshotting/) and [Reconciliation & Offset Surgery](https://sandgraal.github.io/letstalkcdc/reconciliation-surgery/). Raising retention afterwards does not bring purged files back.

### Schema history topic and DDL

The binlog contains DDL as well as row changes. Debezium parses the DDL it meets and keeps an in-memory table structure; separately it writes every DDL statement, with the binlog position where it appeared, to an internal *schema history topic*. After a restart it rebuilds the table structures that existed at its starting position by replaying that topic. The topic is for the connector's own use, and losing it is a separate failure from losing the binlog position: Debezium's `recovery` snapshot mode rebuilds it from the source tables, with the warning not to use it if schema changes were committed after the last shutdown.

- `schema.history.internal.skip.unparseable.ddl` defaults to `false`, which Debezium calls the safe default. Skipping a DDL statement “can lead to data loss or mangling”.
- Online schema-change tools (gh-ost, pt-online-schema-change) create helper tables; Debezium says the connector must be configured to capture them, and an SMT can drop their records if consumers do not need them.
- The connector can also write schema change events to a topic for consumers (`include.schema.changes`). Consumers still own the sink-side DDL decision; see [Schema Evolution](https://sandgraal.github.io/letstalkcdc/schema-evolution/).

### Replicas and failover

Debezium's topology notes: the connector follows *one* server (a primary, or a replica that has its binlog enabled), because it records its position in that server's binlog, and file and position differ on every server. If that server fails, it must come back before the connector can continue. In a highly available cluster that uses GTIDs, Debezium says you can restart the connector immediately: it connects to a different server, finds the location of the last transaction in that server's binlog, and reads from there. For multi-primary setups it can fail over only to a replica that is caught up, and `gtid.source.includes` / `gtid.source.excludes` restrict which GTID sources are used to find the position. `gtid.ignore.on.recovery` appears in the 3.7 reference; check your version before using it.

Two consequences for a sink. First, the new server's file and position are unrelated to the old server's, so a guard that compares them across the switch would skip or accept changes by accident. Treat each server's binlog as its own lineage and bump a lineage number you control at the switch (see the guard); do not derive it from `server_id`, which is not ordered by time. Each event needs its epoch at the moment it is produced, not when it is applied: stamp it in the consumer from a value you change at the switch, and expect events still queued in Kafka from before the bump to carry the old epoch (they are older, so they lose to anything already applied from the new lineage, which is the safe direction; the new server re-sends the same changes under the new epoch). Second, the connector may re-send changes after the switch, now with the new server's coordinates. That is the at-least-once rule again. (MySQL's own docs describe, for replicas, an error when the replica holds transactions the new source does not; I did not confirm what Debezium reports in that case, so make sure the new server has caught up first.)

## MySQL triage and runbook

**Untested.** There is no MySQL in this repository's test setup. The statements below were only parsed (node-sql-parser, MySQL dialect); the two `SHOW BINARY LOG STATUS` forms and `SHOW BINLOG EVENTS` are not accepted by that parser and were taken from the manual. Run them on a test server first.

```
-- 1. Settings that decide whether CDC works, and how long a stopped connector is safe.
SELECT @@global.server_id            AS server_id,
       @@global.server_uuid          AS server_uuid,
       @@global.log_bin              AS log_bin,
       @@global.binlog_format        AS binlog_format,
       @@global.binlog_row_image     AS binlog_row_image,
       @@global.gtid_mode            AS gtid_mode,
       @@global.binlog_expire_logs_seconds AS expire_seconds;

-- 2. The oldest file still on the server. If the connector's stored file is older, it is gone.
SHOW BINARY LOGS;

-- 3. The server's current position. MySQL 8.4 removed SHOW MASTER STATUS (SHOW BINARY LOG STATUS exists from 8.2.0):
SHOW BINARY LOG STATUS;       -- 8.2 and later (the only form in 8.4)
-- SHOW MASTER STATUS;        -- 8.0 and 8.1; deprecated in 8.2 and 8.3, removed in 8.4

-- 4. GTID mode: what was purged, and is the connector's set still serviceable?
SELECT @@global.gtid_executed AS gtid_executed, @@global.gtid_purged AS gtid_purged;
SELECT GTID_SUBSET(@@global.gtid_purged, 'PASTE-THE-CONNECTORS-GTID-SET-HERE') AS position_still_servable;
```

The last query is my derivation from the manual's description of auto-positioning: the source sends everything the replica is missing, and raises error 1789 if any of that was purged. So the purged set must be a subset of what the connector has already seen; `0` means error 1789 is coming. The connector's stored position is in its Kafka Connect offsets; Debezium also exposes `BinlogFilename`, `BinlogPosition`, `GtidSet` and `MilliSecondsBehindSource` as JMX metrics. Pair the lag with `binlog_expire_logs_seconds`: if the connector is further behind than the retention, it is already in the first row below.

| Symptom | Check | Fix |
| --- | --- | --- |
| Connector fails at start; error says the binlog position or GTID is not available (1236, 1789) | Stored file against the first row of `SHOW BINARY LOGS`; in GTID mode, query 4. | The position is lost. Stop the connector, re-snapshot into the idempotent sink (`snapshot.mode=when_needed`, or clear the stored offset and run a snapshot; the schema history topic is separate state, so keep it or rebuild it deliberately, see schema history), then reconcile deletes. Raise retention so it does not recur. |
| Connector is running but far behind | `MilliSecondsBehindSource` and the connector's `BinlogFilename` against the newest file in `SHOW BINARY LOGS`, and against `expire_seconds`. | Fix throughput (Kafka, sink, snapshot contention) before the lag reaches the retention window. Do not lower retention to save disk while the connector is behind. |
| Connector stopped and will be down a while | Time down against `expire_seconds`; managed services: the provider's own setting. | Restart it, or raise retention *now* (`SET GLOBAL binlog_expire_logs_seconds = ...`, and persist it). Raising it cannot recover files already purged. |
| Updates arrive with only some columns, or tables stop producing events after a settings change | Query 1: `binlog_format`, `binlog_row_image`. | Set `ROW` and `FULL`. Changes already logged under another setting are incomplete in the sink: reconcile those tables. |
| Unexpected disconnects after adding another connector or replica | Every `database.server.id` and every replica's `server_id` in the cluster. | Give each client its own ID. (The uniqueness rule is documented; I did not find the exact symptom of a collision in the docs.) |
| Connector fails on a DDL statement | The log line naming the statement; the schema history topic exists. | Prefer fixing the parse or the schema over `skip.unparseable.ddl`. Use `snapshot.mode=recovery` only if the history topic is lost and no schema changed since the last shutdown. |
| After a primary change, errors or gaps | GTID mode on both servers; `GTID_SUBSET` of the connector's set against the new server's `gtid_executed`. | Wait for the new server to catch up, restart the connector, bump the sink's lineage number, and expect replayed changes. |

## SQL Server: capture job, cleanup job and the validity interval

### Two jobs sit between a commit and your connector

Native CDC is enabled per database (`sys.sp_cdc_enable_db`) and then per table (`sys.sp_cdc_enable_table`), and needs SQL Server Agent running. Per Microsoft, the **capture job** reads the transaction log and writes rows into a change table; the **cleanup job** deletes old rows from it. Debezium does not read the log: each cycle it reads the change tables between its last stored maximum LSN and the current maximum, sorts by commit LSN and change LSN, and passes them as the offset to Kafka Connect. Debezium says SQL Server CDC “is not designed to store a complete history of database changes”, which is why there is a snapshot.

| Setting | Default | Effect |
| --- | --- | --- |
| Capture `pollinginterval` | 5 seconds | Wait between log scans. A higher value lowers load and raises latency; changes are not in the change table until a scan has processed them. |
| Capture `maxtrans`, `maxscans` | 500 and 10 (see note) | Transactions per scan cycle and cycles per drain. Throughput is bounded by (maxtrans × maxscans) &divide; polling interval. The `sp_cdc_add_job` reference and Debezium say 500; the “What is change data capture” overview page says 1000. Read your own values with `sys.sp_cdc_help_jobs`. |
| Cleanup `retention` | 4320 minutes (3 days) | How long change rows are kept. One cleanup job serves all capture instances in the database, with one retention value. |
| Cleanup schedule, `threshold` | Daily at 2 A.M.; 5000 | Because it runs once a day, rows can outlive the retention value for a while. Do not plan on that margin. The threshold caps rows deleted per statement. |
| Debezium `poll.interval.ms` | 500 ms | How often the connector checks the change tables. Debezium caps the effective wait at 5000 ms. This adds to, and does not replace, the capture job's polling interval. |

Changes to a job (`sys.sp_cdc_change_job`) do not take effect until the job is stopped and started again. Debezium also warns that the capture job's polling costs CPU on the database host, so lower latency is a trade, not a free setting.

### Retention: the validity interval

Each capture instance has a validity interval: from its low endpoint (`sys.fn_cdc_get_min_lsn`, also the `start_lsn` in `sys.sp_cdc_help_change_data_capture`) to the high endpoint (`sys.fn_cdc_get_max_lsn`, the last LSN the capture process deposited). Microsoft: a request whose low end is left of the interval “could” miss data because of aggressive cleanup, and query functions fail for a range outside it. The cleanup job moves the low endpoint forward. It computes the new low point by subtracting the retention from the *latest captured commit time* (in `cdc.lsn_time_mapping`), not from the wall clock, so a stopped or slow *capture* job does not cause expiry. Your connector being slow does: the cleanup job knows nothing about it.

Debezium's own check, in its source on `main` (I did not read the 3.7 tag): at start it compares the oldest `start_lsn` across the capture instances with the stored commit LSN, and the stored position is valid only if the oldest is not newer than it. Debezium's `when_needed` snapshot mode is documented to snapshot when “a previously recorded offset specifies a log position that is not available on the server”. I did not find a sentence in the reference that says what the default `initial` mode logs in that situation, so check your version, and treat the position as lost either way.

```
-- Raise retention BEFORE a planned outage, not after. 10080 minutes = 7 days: an example, not a recommendation.
EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 10080;
EXEC sys.sp_cdc_stop_job   @job_type = N'cleanup';
EXEC sys.sp_cdc_start_job  @job_type = N'cleanup';
```

The cost of longer retention is change-table size; the cost of shorter retention is a lost position. Size it as policy: longer than the longest connector outage plus catch-up you intend to survive.

### The log that will not truncate

The capture process tells the server's truncation logic how far it has read. Microsoft: with CDC enabled, even in the simple recovery model, the log truncation point “will not advance until all the changes that are marked for capture have been gathered”, and if the capture process is not running, `CHECKPOINT` will not truncate. So a stopped, failing or stalled capture job is SQL Server's version of the Postgres slot problem: the log grows. The error 9002 guide lists `REPLICATION` in `log_reuse_wait_desc` as the state to investigate for replication *or CDC* (and, in full recovery, log backups also gate truncation).

### Schema changes: two capture instances

A capture instance has a fixed column shape. Microsoft: columns added after enabling are ignored, a dropped tracked column yields nulls in later rows, a data type change is propagated, and DDL is recorded in `cdc.ddl_history`. A table can have at most **two** capture instances at once, so a schema change is: create the second instance with a unique `@capture_instance` name, let the connector move to it, then disable the old one. Debezium describes an offline procedure (stop the writers, wait for the connector to stream everything, stop it, apply the DDL, create the new instance, restart) and an online one with no downtime but a window in which events still carry the old shape. It also says that after a NULL/NOT NULL change the connector does not reflect optionality until a new instance exists, and that a table renamed with `sp_rename` keeps emitting under the old name until the connector restarts. Debezium also says SQL Server does not allow a column rename or type change on a CDC-enabled table, while Microsoft's overview says a type change is propagated to the change table: check your version.

```
-- Online schema update (Debezium's documented pattern; SQL Server allows at most two instances per table)
ALTER TABLE dbo.customers ADD phone_number varchar(32);
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'customers',
     @role_name = NULL, @supports_net_changes = 0, @capture_instance = N'dbo_customers_v2';
-- Wait for the connector's notification that it finished the old instance, then:
EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'customers',
     @capture_instance = N'dbo_customers';
```

Two instances capture the same change twice, with the same LSNs, so the position the sink compares does not change at the switch.

### Always On availability groups

- CDC is configured on the current or intended primary. Debezium can read an Always On *read-only replica* (set `driver.applicationIntent=ReadOnly`; the isolation mode becomes `snapshot`), but CDC itself is not enabled on the replica.
- By default the log reader (which CDC uses) does not process log records that have not been hardened at all secondaries. A secondary that is down or behind can therefore hold capture back; Microsoft documents trace flag 1448 for asynchronous secondaries, which is a decision for your DBA, not a tip.
- After a failover, with CDC but no replication, the capture job does not follow the database: run `sys.sp_cdc_add_job` for `capture` and for `cleanup` on the new primary, and disable the CDC jobs on the old one (re-enable them if it becomes primary again).
- `sys.dm_cdc_log_scan_sessions` returns no rows on a secondary and is reset on restart or failover, so latency checks run on the primary. Debezium adds that on a read-only replica its transaction END marker comes only after the next transaction's first event.

## SQL Server triage and runbook

**Untested.** There is no SQL Server in this repository's test setup. These statements were only parsed (node-sql-parser, T-SQL dialect). Read-only checks are safe to try; the `sp_cdc_*` calls change state, so use a test database first.

```
-- 1. Is the Agent running, are both jobs there, what are their settings?
SELECT CASE WHEN dss.[status] = 4 THEN 1 ELSE 0 END AS agent_running   -- needs VIEW SERVER STATE
FROM sys.dm_server_services dss WHERE dss.[servicename] LIKE N'SQL Server Agent (%';
EXEC sys.sp_cdc_help_jobs;

-- 2. Capture instances and their low endpoints (start_lsn); at most two rows per table.
EXEC sys.sp_cdc_help_change_data_capture;

-- 3. Is capture keeping up? session_id 0 is the aggregate row.
SELECT TOP (5) session_id, start_time, end_time, tran_count,
       last_commit_cdc_time, latency, empty_scan_count, error_count
FROM sys.dm_cdc_log_scan_sessions ORDER BY session_id DESC;
SELECT TOP (20) entry_time, error_number, error_message
FROM sys.dm_cdc_errors ORDER BY entry_time DESC;

-- 4. Is the connector's position still inside the validity interval?
DECLARE @stored nvarchar(32) = N'00000027:00000758:0005';   -- commit_lsn from the connector's last event or offset
DECLARE @stored_lsn binary(10) = CONVERT(binary(10), '0x' + REPLACE(@stored, ':', ''), 1);
SELECT sys.fn_cdc_get_min_lsn(N'dbo_customers') AS low_endpoint,
       sys.fn_cdc_get_max_lsn()                 AS high_endpoint,
       @stored_lsn                              AS stored,
       CASE WHEN @stored_lsn >= sys.fn_cdc_get_min_lsn(N'dbo_customers')
            THEN 'inside' ELSE 'PURGED' END     AS verdict;
SELECT sys.fn_cdc_map_lsn_to_time(sys.fn_cdc_get_min_lsn(N'dbo_customers')) AS oldest_change_committed_at;
-- 4b. What Debezium itself checks (per its source on main): the oldest start_lsn across ALL capture instances.
DECLARE @oldest binary(10) = (SELECT MIN(start_lsn) FROM cdc.change_tables);
SELECT @oldest AS oldest_start_lsn,
       CASE WHEN @oldest IS NULL THEN 'no change tables'
            WHEN @oldest <= @stored_lsn THEN 'inside' ELSE 'PURGED' END AS debezium_verdict;

-- 5. Is the log being held back?
SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = DB_NAME();
SELECT total_log_size_in_bytes, used_log_space_in_percent FROM sys.dm_db_log_space_usage;
```

Query 4 is my construction from the documented functions. It checks one capture instance; Debezium's source validates the stored commit LSN against `min(start_lsn)` over `cdc.change_tables`, all instances, which is query 4b. Run both: a table whose own instance was purged is not caught by the minimum over the others. The hex conversion uses style 1, which keeps the `0x` prefix. The CDC functions return `0x00000000000000000000` when the capture instance does not exist or the caller is not authorised, so a zero low endpoint means “check the name and permissions”, not “nothing was purged”.

| Symptom | Check | Fix |
| --- | --- | --- |
| Connector logs “No maximum LSN recorded in the database; SQL Server Agent is not running” | Query 1. (Debezium: a missing maximum LSN is also normal on a new or very quiet database.) | Start SQL Server Agent. While it is down no capture job runs and the log cannot truncate. |
| Events arrive late, change tables lag the source | Query 3: `latency`, `empty_scan_count`, `error_count`; the job values from query 1. | Tune `pollinginterval`, `maxtrans`, `maxscans` with `sp_cdc_change_job`, then stop and start the capture job. Lower latency costs CPU. |
| Connector was down longer than retention; position may be purged | Query 4: verdict, and the oldest change time. | If `PURGED`, the changes between are gone. Re-snapshot into the idempotent sink (`snapshot.mode=when_needed`, or clear the offset), then reconcile deletes. Raise retention with `sp_cdc_change_job` before the next outage. |
| Transaction log keeps growing, disk fills | Query 5: `log_reuse_wait_desc` (`REPLICATION`); queries 1 and 3 for a stopped job or errors. | Start or repair the capture job; in full recovery, keep log backups running. Do not disable CDC to free the log without planning the re-snapshot that follows. |
| New column missing, or wrong nullability, in events | Query 2: only the old instance exists. | Create the second capture instance, let the connector move, drop the old one. If two already exist, wait for the connector's notification that it has finished the older instance (or check that its stored `commit_lsn` is past the newer instance's `start_lsn`), then drop the older one and create the new one. Dropping it earlier discards changes the connector has not read. |
| Rows disappear from the table with no delete events (partition switch) | Whether `ALTER TABLE ... SWITCH PARTITION` or similar bulk operations run on CDC tables. | Microsoft: rows moved by a partition switch are not captured, so the change data is inconsistent with the table. Reconcile the sink after such operations (diff against the source). |
| After an availability-group failover, change tables stop filling (CDC without replication) | Query 1 on the new primary: no capture or cleanup job. If replication is also enabled, the log reader does the capture and nothing more is needed (Microsoft). | `EXEC sys.sp_cdc_add_job @job_type = N'capture'` and `N'cleanup'` on the new primary; disable the old primary's CDC jobs. Expect replayed events. |

## After a restart: duplicates and the position guard

Both Debezium references say the same thing: offsets are committed periodically, not when a change occurs, so after an outage or a crash the connector resumes from the last committed offset and re-emits what came after it. What becomes a duplicate:

- **The tail since the last committed offset.** Same change, same position: `(commit_lsn, change_lsn, event_serial_no)` on SQL Server, `(file, pos, row)` on MySQL. Debezium's MySQL reference points at the binlog file and position (and GTIDs, if used) as the way to recognise a duplicate.
- **A re-snapshot.** Every captured row is emitted again as a read event. For MySQL, all of them carry the binlog position read at the start of the snapshot; for SQL Server, the maximum LSN read then. Rows unchanged in the meantime are a value-equal rewrite; rows deleted in the meantime are not removed.
- **Snapshot signals.** Debezium notes that after a blocking snapshot the connector might emit some events that duplicate rows the snapshot captured.
- **A move to another MySQL server.** The same change can come again with the new server's coordinates, in a new lineage.

Heartbeats (`heartbeat.interval.ms`) can reduce the number of events re-sent after a restart, per the MySQL reference. They do not make delivery exactly-once.

### Building the position for the guard

Key the target on the primary key, store the position next to each row, and apply a change only when its position is newer. Build one fixed-width, zero-padded text key per engine so a plain comparison is a typed comparison, and put a lineage number in front of it. The lineage number (`source_epoch`) is a counter *you* increment when the log lineage changes (a server switch, a binlog reset, a restore): positions are not comparable across lineages.

```
-- Target-side tables, written for PostgreSQL. The pattern is what matters; adapt the syntax to your warehouse.
CREATE TABLE customers_sink (
  id           bigint  PRIMARY KEY,
  email        text,
  deleted      boolean NOT NULL DEFAULT false,   -- deletes stay as markers
  source_epoch int     NOT NULL,
  src_pos      text    NOT NULL
);

-- SQL Server event fields -> one comparable text key (8+8+4 hex digits per LSN, colons removed)
--   replace(coalesce(commit_lsn, '00000000:00000000:0000'), ':', '')
--   || replace(coalesce(change_lsn, '00000000:00000000:0000'), ':', '')
--   || lpad(coalesce(event_serial_no, 0)::text, 10, '0')

-- MySQL event fields -> one comparable text key (file NUMBER, not the name; then pos; then row)
--   lpad(substring(binlog_file from '[0-9]+$'), 10, '0')
--   || lpad(binlog_pos::text, 12, '0')
--   || lpad(coalesce(row_idx, 0)::text, 10, '0')

-- Apply one event ($1..$4 = id, email, op, epoch; $5 = the key built above).
INSERT INTO customers_sink AS t (id, email, deleted, source_epoch, src_pos)
VALUES ($1, $2, ($3 = 'd'), $4, $5)
ON CONFLICT (id) DO UPDATE
SET email = EXCLUDED.email, deleted = EXCLUDED.deleted,
    source_epoch = EXCLUDED.source_epoch, src_pos = EXCLUDED.src_pos
WHERE (t.source_epoch, t.src_pos) < (EXCLUDED.source_epoch, EXCLUDED.src_pos);
```

The guard is strict (`<`), so an event with the same position does nothing: a replay is a no-op, and a late older event cannot overwrite a newer row. That is correct only because the tuple is unique per change; this is why `change_lsn` and `event_serial_no`, and the MySQL `row` index, are in the key and `commit_lsn` or `pos` alone are not. Padding matters because the file name and number are strings that compare wrongly otherwise: `'9' < '10'` is false as text, and a MySQL file number is not guaranteed to keep its width forever.

> **What was run.** The guard and the key expressions ran on PGlite (PostgreSQL 18.3): an insert, an update in the same SQL Server transaction, replays of both, an older update arriving late, a delete, and a replay after the delete all ended on the delete marker; MySQL rows from one binlog event (same file and position, rows 0 and 1) and a file rollover from `.000009` to `.000010` ended on the newest state. The position *values* were hand-written to match the formats above; no real MySQL or SQL Server produced them.

Rows to remember: a snapshot row is just an event with an older (or the snapshot) position, so the guard makes the snapshot and the stream converge; and deletes need their own position, so a replayed insert cannot resurrect a row. The worked patterns are on [Partitioning: version guards and watermarks](https://sandgraal.github.io/letstalkcdc/partitioning/#recon), [Materialization 101](https://sandgraal.github.io/letstalkcdc/materialization/), [Snapshotting: idempotent apply](https://sandgraal.github.io/letstalkcdc/snapshotting/#idempotent) and the [Merge Cookbook](https://sandgraal.github.io/letstalkcdc/merge-cookbook/). Glossary: [idempotent write](https://sandgraal.github.io/letstalkcdc/glossary/#idempotent-write), [effectively-once](https://sandgraal.github.io/letstalkcdc/glossary/#effectively-once), [backfill](https://sandgraal.github.io/letstalkcdc/glossary/#backfill).

> **There is no exactly-once across the whole path.** The connector delivers each change at least once. Whatever a component promises applies to its own hop; the pipeline as a whole is at-least-once delivery plus an idempotent sink, which reaches the same final state. See [Exactly-Once Semantics](https://sandgraal.github.io/letstalkcdc/exactly-once/).

## What was checked, and what was not

- **Read in the primary docs on 2026-10-09:** the Debezium MySQL and SQL Server connector references (stable, labelled 3.7); MySQL 8.4 pages for the binary log options, `PURGE BINARY LOGS`, `RESET BINARY LOGS AND GTIDS`, `SHOW BINARY LOGS`, `SHOW BINARY LOG STATUS`, GTID format, auto-positioning, GTID functions, the server error reference and the 8.4 removals list; the MySQL 8.0 page for `expire_logs_days`; Microsoft Learn pages for CDC concepts, `sp_cdc_enable_table`, `sp_cdc_add_job`, `sp_cdc_change_job`, `sp_cdc_help_jobs`, the CDC functions and views, the administer-and-monitor page, the transaction log guide, error 9002 and availability groups. Parts of the Debezium SQL Server connector source on `main` (the LSN string format, the field mapping, the position validation) were read as well.
- **Run:** the guard SQL and the two key expressions, on PGlite 0.5.8 (PostgreSQL 18.3), with hand-written events. The MySQL and T-SQL statements were parse-checked with node-sql-parser 5.4.0: every statement parsed except `SHOW BINARY LOG STATUS`, `SHOW MASTER STATUS` and `SHOW BINLOG EVENTS`, which that parser does not support (taken from the manual instead).
- **Not run:** any MySQL or SQL Server instance, any Debezium connector, a real purge, a real cleanup, a real failover, an availability group. Everything described as behaviour of those systems comes from documentation, not from observation. In particular not verified: the text of the MySQL error 1236 for a purged file, what the SQL Server connector logs in `initial` mode when its position is below the low endpoint, whether the command id can disagree with the change LSN in direct mode, what Debezium reports when a new MySQL server lacks transactions the connector saw, and the symptom of a duplicate `server_id`. Settings that vary by Debezium release (`gtid.ignore.on.recovery`, `data.query.mode`, the snapshot-mode behaviour) are marked “check your version”.
- **Not covered:** managed services (RDS, Aurora, Azure SQL Database, whose CDC job mechanism differs) beyond what the docs say in passing; MariaDB (different GTIDs); Oracle (see [Oracle notes](https://sandgraal.github.io/letstalkcdc/oracle-notes/)).

### SQL Server and MySQL Knowledge Check

Four questions on who holds the position, how long it stays readable, and what the sink guard compares.

Q1

#### A Debezium MySQL connector has been stopped for 40 days and binlog_expire_logs_seconds is at its default. What do you expect on restart?

A It resumes: the server keeps the binlog files a disconnected consumer still needs

B It cannot resume from its stored position, because the default is 30 days and purged files are not held for a disconnected consumer; it fails, and the way back is a re-snapshot

C It skips ahead to the newest binlog position and carries on without error

D It resumes, because binlog_row_image=FULL pins the files

Q2

#### On SQL Server, which tuple lets a sink tell two changes from one transaction apart and order them?

A commit_lsn alone

B ts_ms, then the Kafka offset

C (commit_lsn, change_lsn, event_serial_no)

D The capture instance name

Q3

#### The SQL Server CDC cleanup job is at its defaults and your connector has been down for four days. What is the risk?

A None: the cleanup job waits for the connector

B The capture job stops, so the transaction log fills

C The connector's stored commit LSN can now be below the low end of the capture instance's validity interval, so the changes in between are gone and a re-snapshot is needed

D The capture instance is dropped and must be recreated

Q4

#### After a restart, the same MySQL change reaches the sink twice. What makes that harmless?

A MySQL guarantees each row event is sent once

B A sink keyed by primary key that applies a change only when its (binlog file number, position, row index) is newer than the stored one, within one source lineage

C Ordering the sink by the event timestamp

D A GTID, because GTIDs make delivery exactly-once

**0/4** correct

## Further resources

### On this site

- [Postgres replication slots & WAL growth](https://sandgraal.github.io/letstalkcdc/postgres-replication-slots/), the same runbook for Postgres.
- [MySQL quickstart](https://sandgraal.github.io/letstalkcdc/quickstarts/quickstart-mysql/) and [SQL Server quickstart](https://sandgraal.github.io/letstalkcdc/quickstarts/quickstart-mssql/) for setup.
- [Ops: Offsets & Replays](https://sandgraal.github.io/letstalkcdc/ops-offsets/) for stored offsets and safe rewinds.
- [Snapshotting](https://sandgraal.github.io/letstalkcdc/snapshotting/) and [Reconciliation & Offset Surgery](https://sandgraal.github.io/letstalkcdc/reconciliation-surgery/) for the re-snapshot and the repair afterwards.
- [Connector Config Builder](https://sandgraal.github.io/letstalkcdc/connector-builder/) for a MySQL or Oracle connector config.
- [Troubleshooting](https://sandgraal.github.io/letstalkcdc/troubleshooting/) and [Observability](https://sandgraal.github.io/letstalkcdc/observability/) for the alerts that should trigger these runbooks.

### Primary sources (read 2026-10-09)

- [Debezium connector for MySQL](https://debezium.io/documentation/reference/stable/connectors/mysql.html) (settings, snapshots and `when_needed`, schema history, topologies, `set-binlog-position`, behavior when things go wrong).
- [Debezium connector for SQL Server](https://debezium.io/documentation/reference/stable/connectors/sqlserver.html) (reading the change tables, `event_serial_no`, capture job parameters, schema updates, Always On).
- [Debezium source: `Lsn.java`](https://github.com/debezium/debezium/blob/main/debezium-connector-sqlserver/src/main/java/io/debezium/connector/sqlserver/Lsn.java) (the LSN string format) and [SqlServerConnection.java](https://github.com/debezium/debezium/blob/main/debezium-connector-sqlserver/src/main/java/io/debezium/connector/sqlserver/SqlServerConnection.java) (column mapping, position validation); `main` branch, not a release tag.
- [MySQL 8.4: binary logging options](https://dev.mysql.com/doc/refman/8.4/en/replication-options-binary-log.html) (`binlog_expire_logs_seconds`, `binlog_format`, `binlog_row_image`) and [8.0](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html) for `expire_logs_days`.
- [MySQL 8.4: PURGE BINARY LOGS](https://dev.mysql.com/doc/refman/8.4/en/purge-binary-logs.html), [RESET BINARY LOGS AND GTIDS](https://dev.mysql.com/doc/refman/8.4/en/reset-binary-logs-and-gtids.html), [SHOW BINARY LOG STATUS](https://dev.mysql.com/doc/refman/8.4/en/show-binary-log-status.html), and the [8.4 removals](https://dev.mysql.com/doc/refman/8.4/en/mysql-nutshell.html).
- [MySQL 8.4: GTID format and storage](https://dev.mysql.com/doc/refman/8.4/en/replication-gtids-concepts.html), [GTID auto-positioning](https://dev.mysql.com/doc/refman/8.4/en/replication-gtids-auto-positioning.html), [GTID functions](https://dev.mysql.com/doc/refman/8.4/en/gtid-functions.html) and the [server error reference](https://dev.mysql.com/doc/mysql-errors/8.4/en/server-error-reference.html) (1236, 1789).
- [Microsoft Learn: What is change data capture](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server) and [Administer and monitor CDC](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/administer-and-monitor-change-data-capture-sql-server) (jobs, defaults, validity interval, monitoring).
- [sp_cdc_change_job](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-change-job-transact-sql), [sp_cdc_add_job](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-add-job-transact-sql), [sp_cdc_enable_table](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql) and [fn_cdc_get_min_lsn](https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-cdc-get-min-lsn-transact-sql).
- [Microsoft Learn: transaction log architecture](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide) (LSN structure) and [error 9002](https://learn.microsoft.com/en-us/sql/relational-databases/logs/troubleshoot-a-full-transaction-log-sql-server-error-9002) (`log_reuse_wait_desc`).
- [Microsoft Learn: replication, change tracking and CDC with availability groups](https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability).

---

CDC: The Missing Manual by Christopher Ennis (Let's Talk CDC), licensed under CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Attribution is required.