Lag, spool and log retention

Understand what pipeline lag measures, how the on-disk spool buffers changes, and how long a source must keep its log before an alert fires.

Three things decide whether a pipeline keeps up and whether it can recover after a pause or an outage: how far the target is behind the source (lag), the buffer on the agent's disk that holds changes in between (the spool), and the log that the source must keep until SQL Flow has read it. This page explains how the pipeline's page reports each one and what you plan for.

What lag measures

Lag is the time between a change committing on the source and the same change committing on the target. SQL Flow reports it in two parts:

The Lag tile on the pipeline's page shows the end-to-end figure, with the two parts and the lag goal beneath it, for example capture 20ms · apply 15ms · goal 100ms. For a pipeline that runs on two agents, the relay bar shows Capture lag and Apply lag for each half. A source monitor has no target, so its tile is Spool backlog. The Replication lag chart on the Overview tab plots the apply part over time.

Lag can only be measured when changes are applied, so the tile shows the last measurement. After 30 seconds it adds measured <time> ago beside the title, and after 60 seconds the figure reads – while the apply part stays in the caption. A pipeline with nothing left to apply reads caught up. When the agent is offline every figure reads –.

Lag goals and colors

Each pipeline is judged against a lag goal. Unless you set one, SQL Flow uses a default that depends on how the pipeline captures and where it writes:

The pipeline gets the loosest goal among its capture mode and its targets. A Fabric Mirror target measures lag to the point where the file lands in OneLake. Fabric's own ingestion into queryable tables comes after that and isn't counted.

The lag figure is gray while it is within range, amber when it is elevated and red when it is high:

A pipeline that stays past its goal appears on the Overview page as Lag above goal.

Set a lag goal

If your own objective differs from the default, record it.

1. Open the pipeline and select the Config tab.
2. In the Lag goal card, In force says whether the goal is yours or SQL Flow's default.
3. Enter the goal in milliseconds, between 1 ms and 24 hours, and select Set goal.

The amber and red bands scale in proportion to your goal. For example, a 5 s goal on a pipeline whose default is 100 ms moves amber to 50 s and red to 125 s. To go back, select Use SqlFlow's default (<value>). The goal changes only what the interface calls late, never how the pipeline captures or applies.

The spool

The agent writes captured changes to a spool on its own disk before it applies them. The spool is encrypted at rest, and lives in the spool folder of the agent's data directory. Each target reads the spool at its own pace, so a slow or paused target doesn't slow the other targets, and doesn't slow capture until the spool reaches its limit.

The spool is what makes an outage harmless. If the target is unreachable, changes wait in the spool and lag grows until the target accepts writes again. Nothing is lost, as long as the spool doesn't reach its limit first.

See the spool

Open Details under the headline metrics on the pipeline's page. The Change spool line shows buffer: <size>, the size on disk. When the backlog grows, it adds an amber backlog: <n> ↑ growing, with an estimate of when it fills when one can be made. A growing backlog means the target is falling behind the source. Check the pipeline's Status card, the target's health and the apply lag.

Plan the disk

The spool may use up to 60 percent of the free space on the volume that holds the data directory, measured when the spool opens. For SQL Server DirectLog and Reconstruction, the agent's capture state shares that volume, so the spool's limit there is roughly a quarter of the free space. Keep the data directory on a volume with room for the longest outage you want to ride out.

The spool needs room for at least one 256 MiB segment. If the volume can't be measured, or is too small, the pipeline doesn't start, with a message that starts retention-cap-underivable or retention-cap-unworkable.

SQL Flow also keeps 256 MiB of the volume free. When free space drops below that, the pipeline shows Degraded with the health reason spool_volume_full, capture waits, and it resumes by itself when space returns.

When the spool reaches its limit

What happens depends on whether SQL Flow holds the source log for the pipeline.

• SQL Server DirectLog that owns the replication truncation point: capture waits, and nothing is dropped. The source keeps the log meanwhile. The pipeline shows Degraded with the health reason spool_full_paused, and returns to Healthy by itself when apply frees space. Fix the target or apply bottleneck, or add disk. If the wait lasts until the log hold reaches its budget, the pipeline fails (see Hold alerts).
• Every other source and mode: the spool deletes its oldest changes, including ones a target hasn't read yet. A target that still needed them can't continue, so its pipeline fails and must be reloaded. Nothing is skipped silently: the target stays at its last committed position. A target that had read further keeps running.

If the spool reseeds

When the spool deletes changes a target still needed:

• The pipeline status becomes Failed. The Failed, needs operator: banner says the spool reached its size limit and dropped changes the target hadn't applied. The pipeline isn't restarted automatically.
• An Error alert titled Pipeline failed appears, with a pipeline.failed entry on the Logs page.
• Before the failure, a warning on the pipeline's Logs tab says the spool reached its size limit.

To recover:

1. Free space on the agent's volume, or move the data directory to a larger one, so the spool can hold the longest outage you expect.
2. Select Actions > Reload from source and confirm. Pause and Start don't clear the failure. See Lifecycle.

Because a reload empties the target tables, treat a growing backlog on the Details line as the warning, and fix the slow target before the spool fills.

Source log retention

The source has to keep the log SQL Flow still needs. A target that is behind never needs more source log, because the spool holds the changes in between. A capture that is stopped, paused or failed does need the source to keep the log from its saved position. Set the source's retention to outlast the longest outage or pause you plan for, and the time you need to react to an alert.

Each of these stops is named, skips no change silently, and isn't restarted automatically, because a restart can't bring the log back. Unless the table says otherwise, recover with a reload: it empties each replicated table and copies it again. For what keeps the log while an agent, broker or target is down, see Source log retention.

The Log retention card

For pipelines that read a raw log, the Overview tab shows a Log retention card. It has one row for each mechanism that keeps the log, such as Truncation-hold pin (open transaction) or Secondary truncation point, with a health chip and facts such as where the hold starts and how much of the source log is used.

Hold alerts

Only SQL Server DirectLog, where SQL Flow owns the replication truncation point, has a hold budget. The budget is 85 percent of what the log can grow to. SQL Flow warns when it projects the log to reach the budget within 24 hours, 6 hours and 1 hour, at the rate the source writes log. Counting the free space the log files can grow into needs VIEW SERVER STATE. Without it, the budget is smaller and is reached earlier. See Source log retention for how the budget is measured.

The alerts appear on the Alerts and Logs pages, and as the pipeline's health reasons: Degraded for the hours warnings and Failed at the budget.

To respond to a warning:

1. If the pipeline is capturing but the target is behind, fix the target or add spool space so apply catches up.
2. If the pipeline failed or its agent stopped, fix the cause and resume it.
3. If you can't in time, pause the pipeline. A pause releases the hold at once. Or delete it if you no longer need it.

See also

• Pipeline detail
• Lifecycle
• Source log retention
• Alerts
• SQL Server DirectLog