Why your SQLite -wal file keeps growing: checkpoints and read transactions that never end

Switch an SQLite database to WAL mode and two files appear next to app.db: app.db-wal and app.db-shm. Usually the -wal file levels off at a modest size, but under some conditions it can keep growing without bound. This post works from the official SQLite documentation as checked on October 9, 2026 (Write-Ahead Logging, PRAGMA, sqlite3_wal_checkpoint_v2(), and the file format) and explains what a checkpoint does and when it stops.

Writing and reading in WAL mode

A database connection’s default journal mode is DELETE (a rollback journal); PRAGMA journal_mode=WAL; switches it. Unlike the other journal modes, WAL mode is persistent: it stays in effect after the connection is closed and the database reopened.

A rollback journal copies the original content into the journal and writes changes straight into the database file. WAL does the opposite. The database file is left alone, changed pages are appended to the end of the -wal file, and a transaction counts as committed once a record marking the commit is appended to the WAL. In the file format document, the unit written to the WAL is a frame, and each frame holds the new content of one page.

When a read transaction starts, it remembers the position of the last valid commit record in the WAL. The documentation calls this the end mark, and it does not change until the transaction ends. So even if another connection commits afterwards, this read transaction keeps seeing the data as of its start. When it needs a page, it looks for the last copy of that page in the WAL before its end mark and falls back to the database file if there is none.

This is why readers and writers do not block each other. There is only one WAL file, though, so only one connection can write at a time. The documentation also lists a few cases where WAL mode still returns SQLITE_BUSY, so do not expect write contention to disappear.

Checkpoints and WAL reset

Moving the content that has piled up in the WAL into the database file is called a checkpoint. A checkpoint first syncs the WAL to disk, then transfers the valid content into the database file, then syncs the database file.

In the default configuration, SQLite runs a checkpoint automatically when a commit brings the WAL to 1000 pages or more. When the last connection closes, it also runs one last checkpoint and deletes the WAL and -shm files. The default of 1000 can be changed at compile time with SQLITE_DEFAULT_WAL_AUTOCHECKPOINT, and at run time with PRAGMA wal_autocheckpoint=N. An N of zero or less turns automatic checkpoints off. Every automatic checkpoint is PASSIVE, and by default it runs on the thread whose commit pushed the WAL over the limit. That is why most commits are fast and the occasional one is noticeably slow.

A finished checkpoint does not make the -wal file smaller. If everything has been transferred to the database and no read transaction is using the WAL, the next write transaction overwrites the WAL from the beginning. This is called resetting the WAL. On a reset the salt values in the WAL header change, which invalidates old frames that were already transferred but not yet overwritten. The file is not shrunk because overwriting an existing file is usually faster than growing one. The documentation describes 1000 pages as about 4 MB. That matches the 4096-byte default page size for new databases since 3.12.0; the actual page size can differ from one database to another.

Why a checkpoint cannot finish

A checkpoint can run alongside read transactions, but it stops when it reaches a page in the WAL that lies past the end mark of any current reader. Writing that page into the database file could overwrite content the reader is still using. The checkpoint remembers how far it got and resumes from there next time.

The official documentation names three ways the WAL can grow without bound.

Automatic checkpoints are disabled. If compile-time or run-time options turn them off or defer them, nothing stops the WAL from growing.
Checkpoint starvation. If read transactions overlap so that there is never a moment with no active reader, no checkpoint can complete and the WAL can never be reset.
Very large write transactions. The WAL cannot be reset in the middle of a write transaction, so a large change produces a large WAL. Since 3.11.0 the WAL for a single transaction is proportional to the amount the transaction changes; in earlier versions the same page could be written to the WAL several times once the transaction outgrew the page cache.

A large WAL costs more than disk space. Readers must check the WAL first for each page, and according to the documentation the time this takes is proportional to the size of the WAL. The wal-index in shared memory speeds up the lookup, but read performance still drops as the WAL grows.

The four checkpointing modes, and NOOP

sqlite3_wal_checkpoint() and automatic checkpoints are always PASSIVE. The other modes are run through sqlite3_wal_checkpoint_v2() or PRAGMA wal_checkpoint(mode).

Mode	Waits for	Writers	On completion
PASSIVE	Nothing. Never calls the busy handler	Not blocked	Transfers as much as it can. Syncs the database file only if every frame was transferred
FULL	No writer, and every reader on the most recent snapshot	New writers blocked while pending	Transfers every frame and syncs the database file
RESTART	As FULL, then until no reader is using the WAL	New writers blocked while pending	The next writer starts the WAL from the beginning
TRUNCATE	Same as RESTART	New writers blocked while pending	Truncates the WAL file to zero bytes just before returning successfully

The current documentation also defines a separate NOOP mode alongside these four. NOOP checkpoints no frames at all, so it does no checkpoint work; it exists only to retrieve the WAL frame count and checkpointed frame count described below. Pass it to sqlite3_wal_checkpoint_v2() or run PRAGMA wal_checkpoint(NOOP);. The pages I checked do not say which version added NOOP, so confirm that your SQLite accepts it before relying on it.

FULL, RESTART and TRUNCATE call the busy handler while waiting for the writer lock and for readers. If the busy handler returns 0, the checkpoint carries on from that point like PASSIVE, transferring what it can, and returns SQLITE_BUSY. If another process is already running a checkpoint, the checkpoint lock cannot be obtained and SQLITE_BUSY comes back immediately, without calling the busy handler. The waiting blocks new writers but not new readers. Even so, the Write-Ahead Logging page lists as a drawback of RESTART and TRUNCATE that readers might block while the checkpoint is running.

Reading the PRAGMA wal_checkpoint result

PRAGMA wal_checkpoint(TRUNCATE); returns one row of three integers.

First column: usually 0. It is 1 if a FULL, RESTART or TRUNCATE checkpoint was blocked from completing by another connection. In C API terms, 0 is SQLITE_OK and 1 is SQLITE_BUSY.
Second column: the number of frames in the WAL.
Third column: how many of those frames have been transferred to the database file, including any transferred before this call.

If the database is not in WAL mode, the second and third columns are -1. After a successful TRUNCATE the WAL is zero bytes, so both are 0. PASSIVE never sets the first column to 1 even when it falls short, so check whether the third column is smaller than the second.

Working through a large WAL

Look for read transactions that never end. If checkpoint starvation is the cause, running PASSIVE checkpoints more often will not get one to complete. The documentation recommends creating reader gaps, moments when nobody is reading, and attempting checkpoints then.
Run PRAGMA wal_autocheckpoint; to make sure automatic checkpoints are not off. If the value is zero or less, check whether the application runs checkpoints itself.
If there are so many readers that gaps rarely occur, run a manual checkpoint in RESTART or TRUNCATE mode. These modes wait through the busy handler, so give that connection one with PRAGMA busy_timeout (or sqlite3_busy_timeout()). Use the first result column to see whether it succeeded.
To limit how much WAL stays on disk after commits and checkpoints, use PRAGMA journal_size_limit=N (in bytes). Each time a transaction commits or the WAL resets, a leftover WAL larger than N is truncated to N. It limits what is left behind, not how large the WAL can get: it is not a hard cap on the peak size, and the WAL can still grow past N while a large write transaction is in progress. The default is -1 (no limit), and 0 truncates to the minimum size every time. The setting applies only to the one database it is issued for.

PRAGMA wal_autocheckpoint;         -- auto-checkpoint interval in pages
PRAGMA journal_size_limit;         -- WAL size kept after commit/reset, bytes; -1 means no limit
PRAGMA busy_timeout = 5000;        -- example value, in milliseconds
PRAGMA wal_checkpoint(TRUNCATE);   -- returns: blocked flag, WAL frames, frames transferred

Do not delete the -wal file by hand. The WAL file is part of the database’s persistent state, and separating it from the database file can lose committed transactions or corrupt the database. Copy or move it together with the database. The only safe way to remove it, according to the documentation, is to open the database with one of the sqlite3_open() functions and immediately close it with sqlite3_close(); when the last connection closes cleanly, it checkpoints and then deletes the WAL. If a process exited without closing cleanly, or SQLITE_FCNTL_PERSIST_WAL was used, the WAL can remain after every connection has closed.

The documentation also describes the WAL-reset bug, fixed in March 2026. It is a data race that can, in rare cases, corrupt a database when two or more connections in separate threads or processes have the same file open and try to write or checkpoint at the same instant. It is likely present in 3.7.0 through 3.51.2 and is fixed in 3.51.3 and later, with the fix backported to 3.44.6 and 3.50.7. The developers say it is unlikely in common use and not an emergency, but recommend upgrading because the consequences are serious.

A WAL read transaction that keeps seeing the data as of its start resembles a PostgreSQL snapshot. When PostgreSQL takes its snapshots is covered in READ COMMITTED and snapshots.

Sources

SQLite — Write-Ahead Logging: sections 1, 2.1, 2.2, 2.3, 3, 3.1, 3.2, 3.3, 4, 6, 9, 11
SQLite — PRAGMA Statements: wal_checkpoint, wal_autocheckpoint, journal_size_limit, journal_mode, busy_timeout, page_size
SQLite — sqlite3_wal_checkpoint_v2()
SQLite — Database File Format: sections 4.1, 4.3, 4.4, 4.5

Links and content checked on October 9, 2026.