.DB-JOURNAL File Format

Introduction

A -journal file is the rollback journal of an SQLite 3 database. It sits in the same folder as the database and takes its full name with -journal added: app.db has app.db-journal. Before SQLite changes a page of the database, it copies the page’s original content into the journal. If the program or the computer stops in the middle of a transaction, the journal is still there, and the next program to open the database uses it to put the original pages back.

This is SQLite’s original way of keeping a database safe, used by every SQLite 3 and still the default. Normally the journal exists only while a transaction is writing, and is deleted, emptied or cleared when it commits; a -journal file that stays beside a database is a sign that a program stopped mid-transaction. SQLite documents the format in full. Only SQLite writes it.

A -journal file dropped on any page of this site opens in the explorer, which shows the header and every page record, and checks each record’s checksum.

Structure

A header padded to the size of one disk sector, then the page records. Every number is big-endian. A journal can hold more than one segment, each a header and its records.

Journal header

A journal is valid only while its header is. Committing makes it invalid: SQLite deletes the file, truncates it to nothing, or overwrites the header, usually with zeros, depending on PRAGMA journal_mode. A journal left in any of those states is not this format any more and holds nothing to play back.

In journals made for this page, a journal copied before SQLite had synced it to disk had its first 12 bytes, the header string and page count, still zero. SQLite fills them in when it syncs the journal, before it changes the database, so a journal caught in that state is not yet valid either.

Page records

Each record holds one database page as it was before the transaction changed it. Records follow the header, from the first sector boundary, one after another.

A page is saved once per transaction, the first time it is changed. Pages added to the database by the transaction are not saved: truncating the database to the initial size removes them.

To roll back, SQLite reads the records in order and writes each original page back to its place in the database, stopping at the first record whose checksum does not match. Then it truncates the database to the initial size and makes the journal invalid.

Checksum

The checksum guards against a record torn by a power failure, half old and half new. It samples every 200th byte of the page, from near the end:

1. Start with sum = the nonce from the header.
2. Let X = page size − 200.
3. While X ≥ 0: add the byte at offset X of the page to the sum, then subtract 200 from X.
4. The checksum is the sum, in 32 bits.

With 1,024-byte pages it adds the bytes at offsets 824, 624, 424, 224 and 24; with 4,096-byte pages, 20 bytes from offset 3,896 down to 96. A page whose sampled bytes are all zero has a checksum equal to the nonce.

Segments

A journal may hold more than one segment. Each starts with its own header at a sector boundary, followed by its own records, and a reader plays back each segment in turn. The explorer reads the first segment and shows whatever follows its records as bytes: later segments, or old content left from an earlier transaction.

A real journal

A rollback journal made with the sqlite3 shell for this page, copied while the transaction was open: a database of two 1,024-byte pages with 12 bytes reserved at the end of each, holding a table t with the row (1, 'one'), and the transaction UPDATE t SET b='uno'. The journal is 1,544 bytes: a 512-byte header sector and one record of 4 + 1,024 + 4 bytes.

00000000  D9 D5 05 F9 20 A1 63 D7 FF FF FF FF 94 72 0C C2
00000010  00 00 00 02 00 00 02 00 00 00 04 00 00 00 00 00
…
00000200  00 00 00 02 0D 00 00 00 01 03 EC 00 03 EC 00 00
…
000005F0  06 01 03 09 13 6F 6E 65 00 00 00 00 00 00 00 00
00000600  00 00 00 00 94 72 0C C2

• The header string, then a page count of −1. This journal was written with PRAGMA synchronous=OFF, where SQLite leaves the count as −1 and the records run to the end of the file.
• Nonce 94 72 0C C2. Initial size 00 00 00 02, 2 pages. Sector size 00 00 02 00, 512. Page size 00 00 04 00, 1,024.
• At offset 512, the first sector boundary, the record: page 2, then page 2’s original content. It is a leaf table page with one cell at page offset 1,004, which is journal offset 4 + 512 + 1,004 = 1,520 (5F0): 06 01 03 09 13 6F 6E 65, the row (1, 'one') as it was before the UPDATE.
• The checksum at offset 1,540 is 94 72 0C C2, the nonce itself: the bytes at page offsets 824, 624, 424, 224 and 24 are all zero.

SQLite 2 journals

SQLite 2 also wrote -journal files, in other layouts, with header strings that end in D4, D5 or D6 instead of D7. They are not this format, and the explorer has no parser for them.

Telling it apart

The file starts with the 8 bytes D9 D5 05 F9 20 A1 63 D7.

The explorer’s parser checks those 8 bytes and needs the 28 bytes of the header; a file that fails either is turned down. It reports a page size that is not a power of two from 512 to 65,536, a sector size it cannot use, a page count larger than the file holds, and every record whose checksum does not match, and still shows the file. No rollback journal was among the files examined for this site; the parser was tested with journals made for the purpose.

Related formats

• SQLite 3 database – the database the journal belongs to
• SQLite write-ahead log (-wal) – the other way SQLite protects a transaction: the new pages instead of the old

Links