SQLite write-ahead log (-wal)

In write-ahead logging (WAL) mode, SQLite does not overwrite the main database file when a transaction commits. It appends the modified pages to a sidecar file named <database>-wal, and readers consult that file first. Periodically a checkpoint copies the pages back into the main database; by default this happens automatically once the WAL reaches about 1,000 pages. A companion -shm file holds a shared-memory index of the WAL.

For investigators this has three consequences. First, the most recent activity may exist only in the -wal file, so the database, -wal and -shm must always be collected together. Second, the WAL can contain several versions of the same page, which sometimes preserves records that were later edited or deleted in the main file. Third, simply opening the database with an ordinary SQLite client can trigger a checkpoint, and closing the last connection normally merges and removes the WAL — altering evidence. Work on copies, and parse the WAL separately when earlier page versions matter.

Windows artifacts that rely on SQLite in WAL mode include Windows Timeline (ActivitiesCache.db), the notification database (wpndatabase.db) and Firefox profiles among the browser artifacts. The idea mirrors the transaction logs of the ESE database format and the registry transaction logs: pending data lives outside the main file until it is merged.