Styxis
Company
Knowledge

SQL Server when_needed: do not accept a NULL LSN as a recovery position

Separate an offset record from a usable LSN and verify the required snapshot actually starts.

An offset row can hide an unknown position

An offset record is different from a usable commit LSN. Do not approve a NULL LSN as a recovery position after CDC recreation. The useful Truthound comparison is the required recovery route against the route actually selected; inventing a valid LSN for an unknown value can hide missing initial data. [1, 2, 3]

Separate test evidence from causal hypotheses

Debezium SQL Server #2986 reports a failing when_needed integration test in which a snapshot saved a NULL commit LSN and a later restart after CDC recreation skipped a needed snapshot. The report marks two explanations for how the LSN arose as unconfirmed. The test and logs do not establish a customer loss total or a confirmed root cause. [1]

Check the snapshot that recovery requires

Isolate runs with a stored NULL commit LSN after a valid source minimum LSN has been confirmed following CDC recreation. Withhold recovery completion until resnapshotting and source-row reconciliation finish. Record the exact snapshot.mode=when_needed version and CDC history, then distinguish route selection, actual startup and landed data. [1, 3]

What one selected route establishes

The illustrative policy requires a new snapshot when no usable LSN exists, so required_snapshot_flag=1 for the NULL case. selected_snapshot_flag=1 meets only the route-selection condition. A value of 0 triggers investigation of offset-presence-based skipping. A selected 1 still does not prove snapshot completeness or completion; source reconciliation remains necessary. [1, 3]

Compare required and actual routes

Truthound Core Python SDK 3.3.2 can compare prepared required_snapshot_flag and selected_snapshot_flag using ColumnPairValidator with relationship="==". The operator verifies NULL state and the available minimum LSN, then supplies non-missing 0 or 1 policy flags. The SDK does not change SQL Server log retention or connector offsets. [1, 2, 3]

Even forced snapshots need a condition

Use a verified target version and the operating recovery procedure, then reconcile source rows after resnapshotting. Forcing a snapshot when a valid resume LSN exists can create unnecessary duplication and load. Keep unverifiable source scope unresolved instead of declaring recovery complete. [1, 3]

Application conditions

Confirm the exact snapshot.mode=when_needed contract and CDC recreation history, distinguishing an available source minimum LSN from stored NULL.

Alternatives

Resnapshot with a verified target version and the operating recovery procedure.

Limitations

  • Truthound does not change SQL Server log retention or offsets; test loss counts do not establish customer harm.

Evidence

Event checked
Subject checked
Modified