You deleted the -wal. If a writer was still running, nothing was lost.
The same command, on two databases that look alike, and no error either way. Running
rm app.db-wal while the application still had it open lost nothing: the writer kept reading
all 500 rows, committed one more, and the database was intact when it closed. Running the identical
rm app.db-wal on a stopped copy left 0 of those 500 rows, PRAGMA integrity_check
answered ok, and nothing anywhere said a word.
“A
-walfile is not a log you can safely tidy up. While a process holds it, deleting it is free — the kernel keeps the unlinked file alive for that process. When nobody holds it, the commits in it are gone, and the.dbthat remains is not corrupt: it is a smaller, consistent, older database.”Measured on SQLite 3.45.1 (Python 3.12
sqlite3), Linux ext4, 2026-11-16.
Both cases, measured
Setup: users with 500 rows, WAL mode, wal_autocheckpoint=0. The table was
checkpointed into the .db first, so the .db is a complete 2-page database and every one of
the 500 rows exists only in the -wal. The snapshot is taken with the writer still open — the state a
crash leaves behind, and the only state in which a -wal survives a normal close.
| What was done | Files after it | What the file then held |
|---|---|---|
| nothing — the snapshot as a crash leaves it | .db 8,192 B · -wal 24,752 B (6 frames) · -shm 32,768 B | 500 rows, integrity_check ok, page_count 6 |
rm main.db-wal, -shm left in place |
.db 8,192 B · no -wal · -shm 32,768 B | 0 rows, integrity_check ok, page_count 2 |
rm main.db-wal main.db-shm |
.db 8,192 B · no -wal · no -shm | 0 rows, integrity_check ok, page_count 2 |
rm main.db-wal while the writer is still running, one more commit, then close |
.db 8,192 B → 24,576 B on close · no -wal · -shm 32,768 B | 501 rows, integrity_check ok, page_count 6 |
Why the two rms differ
On Linux, removing a file that another process has open only removes the name. The inode and its pages
stay alive for that process until the last descriptor closes. SQLite's writer kept appending frames to the
unlinked -wal, so the 500 rows were never out of reach; the only visible change was that
main.db-wal no longer appeared in ls (a second process could not open it, and a
cp of the trio would no longer find it). At close() the writer checkpointed normally and
wrote the whole database back into the .db: 8,192 B → 24,576 B, 6 pages, 501 rows,
integrity_check ok.
With no process holding the file there is nothing to keep it alive. The 24,752 bytes are freed, and what
remains is the 8,192-byte file that existed before the rows were written — which is a perfectly valid database,
just an older and emptier one. quick_check agreed with integrity_check. The
users table is still there; it has no rows.
The state that kills: consistent, empty, and green
Nothing about the surviving file is damaged, which is why the usual checks are useless here.
PRAGMA integrity_check → ok. sqlite_master → the table you
expect. SELECT count(*) → 0. Header page_count at offset 28 →
2, and the file is exactly 2 × 4,096 bytes, so even a byte-level size check finds nothing wrong.
The freelist is empty, so the rows are not recoverable as free-page residue either — they were never
written into this file.
Two bytes still give the history away: the file-format write and read versions at offsets 18 and 19 are
2 and 2, meaning this database was in WAL mode. A WAL-mode database with no
-wal beside it, holding 2 pages where its table should need more, is either freshly checkpointed or
missing its commits; the page counts decide it. A 32 KB -shm left next to an 8 KB .db
with no -wal is the other fingerprint: something copied the sidecar files and dropped the one that
carried the data. The -shm itself holds no rows — it is shared memory of the index, and deleting it
along with the -wal changes nothing.
How to tell after the fact
import os, struct, sys
p = sys.argv[1]
h = open(p, "rb").read(100)
ps = struct.unpack(">H", h[16:18])[0] or 65536
declared = struct.unpack(">I", h[28:32])[0]
wal = os.path.exists(p + "-wal")
print("%s: wal_mode=%s wal_file=%s pages=%d file_bytes=%d"
% (p, h[18:20] == b"\x02\x02", wal, declared, os.path.getsize(p)))
If that prints wal_mode=True wal_file=False for a database whose tables you know had rows, stop
looking for corruption. Run the file through the checker above as well: it reports
WAL_MODE_BUT_NO_WAL and CRITICAL for exactly this state, and
MAIN_DB_STALE when the pair is still together and the .db is behind the
-wal — the state in the first row of the table, where copying only the .db is the
mistake about to happen.
Order of operations
- Do not
rma-wal, ever. It is part of the database, not a temporary file, and it is only safe to remove when SQLite itself removes it — at the last connection's clean close. - Moving a live database:
VACUUM INTO 'copy.db'or the.backupcommand produce one consistent file;sqlite3_rsyncfor a target you can reach. Copyingapp.db,app.db-walandapp.db-shmtogether is the fallback, and only while every writer is stopped. - If the
-walis already gone, the rows are not in the.dband no repair tool can invent them: restore from a backup that contains the pair, and expect the file you have to test clean. Check it anyway — knowing it is empty-but-valid rather than corrupt is what tells you a restore is needed instead of a repair.
What was not tested, stated plainly: one Linux host on ext4, SQLite 3.45.1, Python's sqlite3
module. The live-unlink row depends on POSIX semantics — on Windows an open file cannot be deleted, so the
rm fails outright and no data is lost. No NFS/SMB share, no O_DIRECT, no
FULL-sync power-cut run with real hardware was included, and nothing here is a claim about what a
filesystem with broken locking does.
Check one .db file (max 20 MB; add the -wal if you have it)
Read-only byte inspection: page counts, WAL commit, freelist residue. The file is deleted before the answer is written, and nothing about its content is logged.
One-line check — no browser, same endpoint, JSON out
The request body is the file. Answer is a stable dbcheck/1 document, so it can go
straight into a script or a monitoring job:
curl -sS --data-binary @backup.db -H 'Content-Type: application/octet-stream' https://huxin.online/dbcheck/check
If you still have the -wal, send both parts. Order matters, not the field names: the first
file part is the .db, the second is its -wal.
curl -sS -F 'file=@backup.db' -F 'wal=@backup.db-wal' https://huxin.online/dbcheck/check
Read verdict (ok / warn / critical) and the
findings[].code strings; exit_code is 0 only when the verdict is ok.
Limits: 20 MB for the .db, 4 MB for the -wal, 10 s of wall clock, 2 audits at a
time — the third gets 429 BUSY. The upload is deleted before the answer is written and no
byte of it is logged. Capabilities: api/info.