A large SQLite -wal file after a checkpoint is often normal: checkpointing copies eligible changes into the main database, but SQLite usually keeps the WAL allocated so it can reuse the space. To make the file smaller, request a truncating checkpoint with PRAGMA wal_checkpoint(TRUNCATE); and check whether it completed. If readers or an active write prevent completion, the file may stay large.
Why a successful checkpoint can leave a large WAL file
In WAL mode, committed changes are first recorded in the write-ahead log. A checkpoint transfers eligible WAL frames into the main database file. That transfer and shrinking the WAL are separate operations: SQLite normally recycles the existing WAL and overwrites it from the beginning rather than truncating it.
As an Amazon Associate I earn from qualifying purchases.
Consequently, file size alone does not tell you whether changes remain uncheckpointed. The SQLite Project’s Write-Ahead Logging guide says: “The checkpoint does not normally truncate the WAL file (unless the journal_size_limit pragma is set).” A large file can be available for reuse even after checkpointing has made progress.
Recommended Free Tools
What can keep the WAL from resetting or make it grow?
| Cause | What happens | What to check |
|---|---|---|
| Reusable allocation | The checkpoint copies eligible frames, but the file remains allocated for reuse. This is normal behavior, not proof of a problem. | Check the checkpoint result rather than judging by file size alone. |
| Readers holding old snapshots | A reader may still need older WAL content. SQLite cannot reset the WAL while doing so would remove data that reader needs. Long-lived or overlapping read transactions can prevent checkpoint completion and allow the WAL to grow. | Look for open read transactions, cursors, or idle connections that still hold a read snapshot. |
| Automatic checkpointing changed or disabled | Automatic checkpointing may be disabled, configured with a different threshold, or affected by application checkpoint-hook behavior. | Inspect PRAGMA wal_autocheckpoint; and the application’s SQLite setup. |
| Large active write transaction | A write transaction can append many frames. SQLite cannot reset the WAL in the middle of that transaction. | Check whether a large write is still in progress and how long transactions remain open. |
Automatic checkpointing is a threshold, not a zero-size guarantee
SQLite’s documented automatic-checkpoint default is 1000 WAL frames per connection, unless the build-time default or runtime configuration changes it. Automatic checkpoints use PASSIVE behavior, which makes the progress allowed by concurrent activity rather than forcing all readers out of the way. The SQLite Project’s WAL guide describes normal operation as appending to roughly 1000 pages—about 4 MB in its example—then checkpointing and reusing the WAL. That byte estimate depends on database page size; it is not a universal maximum or a promise that the file will shrink to zero.
#1 Best Overall
How to find out whether checkpointing is blocked
- Confirm the live database and WAL. Verify that the application is using the database file you are inspecting and that it is in WAL mode. The sidecar is normally named by adding
-walto the database filename. - Inspect the checkpoint result. Run
PRAGMA wal_checkpoint(PASSIVE);on a connection to that database. The pragma returns status and WAL-frame information; examine the returned values rather than treating a successful SQL call as proof that every frame was checkpointed. - Check automatic-checkpoint configuration. Run
PRAGMA wal_autocheckpoint;. SQLite’s documented default is 1000 frames, but a different value may be configured. Zero or a negative value disables the automatic threshold. Also check whether application code installs a WAL hook or otherwise manages checkpoints. - Review connection and transaction lifetimes. Find readers that retain a snapshot, including code that leaves a read transaction or cursor open while idle. Look for long-running or overlapping reads, as well as write transactions that are still active.
- Retry when readers have released old snapshots and writes have finished. A checkpoint can make more progress once the connections that need older WAL content have ended their read transactions.
How to request that the WAL shrink
After addressing blockers, run this on a writable connection to the database:
PRAGMA wal_checkpoint(TRUNCATE);TRUNCATE requests a checkpoint and truncates the WAL to zero bytes after successful completion. Inspect the pragma’s returned status and frame information to confirm the result. If concurrent database activity prevents completion, do not assume that the file was truncated just because the statement ran.
Rank #2
| Mode | What it requests | Trade-off |
|---|---|---|
PASSIVE |
Checkpoint as much as possible without waiting for readers or writers to finish. | Less disruptive, but it may leave work outstanding and does not request truncation. |
TRUNCATE |
Complete the checkpoint when possible and truncate the WAL. | Can wait for or interfere with concurrent database activity; schedule it for an appropriate quiet period and verify its returned status. |
SQLite also provides FULL and RESTART checkpoint modes. They can be blocked by concurrent database use and may make readers wait; select a mode based on the workload rather than using a forceful checkpoint as a substitute for fixing connections that hold snapshots open.
Do not delete or move the WAL on its own
While connections are open, the WAL is part of the database’s persistent state. Removing or moving it separately from the database can discard committed transactions or corrupt the database. For a live copy, use SQLite’s supported backup mechanisms. For file-level handling, close all connections cleanly first, and keep the database and its WAL together until that is done.
Quick Recap
Rank #4
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
