High availability
Source log retention
Learn what keeps each source's transaction log while a component is down, how pause releases it, and how pipelines resume from log backups.
Log-based capture needs the source to keep its transaction log until SQL Flow has read it. While an agent, a log broker or a target is down, the source keeps more log than usual, or truncates log the pipeline hasn't read yet. This page explains, per source, what holds the log and what happens when it's gone.
What holds the log, per source
| Source and capture mode | What keeps the log SQL Flow needs | While the pipeline is paused |
|---|---|---|
| SQL Server DirectLog | SQL Flow holds the database's replication truncation point until each target commits the changes. | Released at once. |
| SQL Server Reconstruction and Polling | Nothing SQL Flow sets. The online log and your log backup chain. | Nothing is held. |
| SQL Server Native CDC and Azure SQL | SQL Server's CDC capture job, and the change tables for the cleanup job's retention period. | The change tables keep changes for the retention period. |
| Oracle DirectLog and LogMiner | Nothing SQL Flow sets. Your online and archived redo logs. | Nothing is held. |
| PostgreSQL | The replication slot, from the last position SQL Flow confirmed. | The slot stays and keeps WAL. |
| MySQL | Nothing SQL Flow sets. Your binary logs. | Nothing is held. |
| Sybase ASE DirectLog and Reconstruction | SQL Flow holds the database's secondary truncation point while the capture runs. If the agent stops without releasing it, it stays set and the log grows until the agent or its standby returns. | Released when the capture stops. |
Where nothing is held, the source truncates its log on its own schedule. If the pipeline needs a position the source no longer has when it resumes, it stops with a retention error and you reload the pipeline, unless it can continue from log backups (SQL Server only, below).
max_slot_wal_keep_size. A Sybase ASE capture keeps the log from being truncated past the point where it started for as long as it runs. Size these logs for the time a pipeline can be paused or down, and watch them.SQL Server DirectLog: the hold
DirectLog marks the captured tables for replication, so SQL Server keeps every log record of those tables until SQL Flow releases it. SQL Flow moves the truncation point forward as each target commits. The database shows REPLICATION in log_reuse_wait_desc while it waits for SQL Flow.
While a component is down
| What is down | Effect on the hold |
|---|---|
| The agent | Nothing moves the truncation point forward. The log grows until the agent, or its standby, is back. |
| The log broker | Capture stops, but the agent keeps managing the hold and its budget. The log grows by what the source writes meanwhile. |
| The target | The hold stays at the position the target last committed. The log grows until the target accepts writes again. |
| The link between relay agents | The hold stays at the position the target last committed, as above. |
The 85 % budget
The agent protects the source from a hold that would fill its log:
- Capacity is the log's allocated size plus the growth its files may still take, limited by the free space of each volume. Measuring free space needs
VIEW SERVER STATE. Without it, SQL Flow counts no growth at all, so the budget is reached earlier, never later. - Budget is 85 % of the capacity.
- Hours to budget is the time left until the log reaches the budget at the rate the source writes log. SQL Flow raises an alert when it falls under 24 hours, 6 hours and 1 hour.
- At the budget, and only while SQL Flow's hold is what keeps the log, the hold is released so the database can truncate its log. A capturing pipeline stops with
source_log_hold_budget_exceeded. When it resumes, it continues from the online log or the log backups if they still hold its position, and otherwise needs a reload.
Pause releases the hold
Pausing a DirectLog pipeline releases its hold at once, so a paused pipeline never fills the source's log. When you start it again, it continues from the online log while the database still has that position, otherwise from the database's log backups. Otherwise it stops with source_log_gone_after_pause, which names when it was paused, and you reload the pipeline or its tables.
If a resume will likely need a reload, because the database uses the SIMPLE recovery model, has no log backup recorded, or keeps its backups on media SQL Flow can't read, the paused pipeline carries the warning source_log_resume_may_need_reload.
Delete releases the marking
Deleting the last DirectLog pipeline of a database removes the marking SQL Flow added, and the log can truncate again. Archiving or moving a pipeline keeps the marking, because the pipeline may still need it.
Resume from log backups
SQL Server DirectLog, Reconstruction and Polling can continue from the database's log backups when the online log no longer has their position:
- DirectLog and Reconstruction read the backup files through the log broker. Include the log backup folders in the broker's allowed folders.
- Polling reads them through SQL Server, so its login needs
sysadmin.
The backup chain must be unbroken from the pipeline's position to the online log. SQL Flow reads native SQL Server log backups written to disk files, not encrypted, not striped and not containing bulk-logged operations. For the exact rules, including which compressed backups each version can use, see Log backups SQL Flow can read and Polling.
Monitor the hold
The Log retention card on the pipeline's Overview tab shows each mechanism that holds the source log:
- Pins the source log: whether this mechanism holds the log for this pipeline.
- Held from or Required from: the position the log is kept from.
- Source log used: how full the log is.
- log_reuse_wait: what SQL Server reports the log is waiting for.
- A health chip: ok, Source log budget: within 24 hours, within 6 hours, within 1 hour, or Source log hold budget exceeded.
The agent also raises these alerts:
| Alert | Severity | Meaning |
|---|---|---|
source_log_hours_to_loss | Warning | The hold may reach its budget within 24, 6 or 1 hours. |
source_log_hold_budget_exceeded | Error | The hold reached its budget and was released. |
source_log_resume_may_need_reload | Warning | A paused pipeline will likely need a reload when it resumes. |
source_truncation_point_held_by_another_session | Error | Another session is the database's log reader, so SQL Flow can't advance or release anything. |
Troubleshooting
Log hold approaching its budget
The source log hold may reach its budget within …The source log that SQL Flow holds keeps growing toward its budget. For a pipeline that is capturing, restore spool capacity or apply progress so the target catches up. For a pipeline that isn't capturing, resume it, pause it to release the hold at once, or delete it.
Log reader role held by another session
source_truncation_point_held_by_another_sessionAnother session on the source server holds the log reader role of the database and refused sp_repldone (Msg 22903), for example a transactional replication Log Reader Agent. SQL Flow keeps retrying, and the log grows meanwhile. End that session to give the role back.
For source_log_gone_after_pause, source_log_hold_budget_exceeded, a log backup chain gap, and a log that still waits on REPLICATION after a pipeline was deleted, see SQL Server DirectLog: Troubleshooting.
