State store (DuckDB)

The agent keeps all of its memory between runs in one local DuckDB file, dlh_agent_state.duckdb by default (path set by state_db_path, relative to the install directory). It is what lets a run every five minutes know exactly which Change Tracking version or CDC LSN each of thousands of tables reached last time.

What it contains

Credentials and the bootstrap cache are stored elsewhere (encrypted .sec files next to the EXE and the encrypted cache under %PROGRAMDATA%\DLH\dlh-agent-sql-server), so the state file can be shared with support without redaction. It is opened exclusively by the running agent; a second process (including --diagnose, which opens it read-only) sees a lock while a run is in progress.

Why it matters

• Pointer safety. A table's CT version or LSN is advanced only after its data has been written successfully. A failed table keeps its old pointer and retries the same window next run; other tables continue. Nothing is skipped silently.
• First run detection. A table with no sync_state row is loaded in full regardless of its configured sync mode, then tracked incrementally.
• Retention fallback. If the stored CT version is older than SQL Server's minimum valid version (retention expired), the agent logs it and reloads that table in full instead of producing a gap.
• Two-stage drift handling. fail_once records the drifted table here so the next run can apply the configured action to exactly that table.
• Telemetry and diagnostics. The startup telemetry block and --diagnose read it to report tables tracked, last success, failed tables and pending drift.

Sizing and growth

The file is small relative to your data: tens of megabytes for a few thousand tables. run_history is the only table that grows without bound (one row per table per run; 300 tables every five minutes is about 86,000 rows a day). The telemetry block prints the file size and WAL size on every run so growth is visible; if the file becomes inconveniently large you can archive the history (below) without affecting sync pointers.

Backup

Back up the state file whenever you back up the install directory, and always before an upgrade or a configuration change that alters sync modes:

Disable-ScheduledTask -TaskName "DLH.io SQL Server Agent X"
# wait for any running instance to exit, then
Copy-Item "C:\Program Files\DLH\dlh-agent-sql-server\dlh_agent_state.duckdb" `
          "D:\backups\dlh_agent_state_$(Get-Date -Format yyyyMMdd_HHmm).duckdb"
Enable-ScheduledTask -TaskName "DLH.io SQL Server Agent X"

Copy the .wal file too if one exists. Do not copy the file while a run holds it open.

Losing the file is recoverable but expensive: every table is reloaded in full on the next run, which with merge produces the same target data but may take hours and, for CSV and Parquet outputs, re-emits every row.

Inspecting the state

For most questions use the built-in views:

• dlh_agent_sql_server.exe --diagnose prints tables tracked, last successful run, tables whose last run failed and pending drift.
• --diagnose --json gives the same as structured data (state_store section).
• Every run log starts with the telemetry block.

For ad hoc queries, copy the file (not the live one) and open it with the DuckDB CLI or any DuckDB client:

-- Tables that failed on their most recent run
SELECT database_name, schema_name, table_name, status, error_message, finished_at
FROM run_history
QUALIFY ROW_NUMBER() OVER (PARTITION BY database_name, schema_name, table_name ORDER BY started_at DESC) = 1
  AND status = 'error';

-- Current pointer per table
SELECT database_name, schema_name, table_name, sync_mode, last_ct_version, last_lsn, last_sync_at, last_row_count
FROM sync_state ORDER BY 1, 2, 3;

-- Rows extracted per run over the last day
SELECT run_id, MIN(started_at) AS started, SUM(row_count) AS rows, COUNT(*) AS tables,
       SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END) AS failed
FROM run_history
WHERE started_at > now() - INTERVAL 1 DAY
GROUP BY run_id ORDER BY started;

Supported resets

When you rename or remove the state file and the target is Delta or Iceberg, existing target tables are kept and the full reload is merged into them by primary key. For append strategies or CSV and Parquet output, a full reload emits every row again.

Archiving run history

If run_history growth becomes a concern, stop the task, copy the file, then on the copy:

DELETE FROM run_history WHERE started_at < now() - INTERVAL 90 DAY;
CHECKPOINT;

Swap the trimmed copy back into place while the task is still stopped. Sync pointers are in sync_state and are unaffected. Keep the original until the next run has succeeded.

Moving or renaming the file

state_db_path accepts an absolute path, so the state file can live on a different volume from the install directory (for example a volume with snapshots). Stop the task, move the file, update the path, run --diagnose to confirm the agent sees an Existing database with the expected number of tables tracked, then re-enable the task.