SQLite: database or disk is full

What it means

A write needed another page of space and couldn’t get it. SQLite returns SQLITE_FULL (code 13), “database or disk is full”. The write can be to the database file, its journal or WAL file, or a temporary file SQLite uses for sorting, temporary tables and VACUUM.

The statement fails. SQLite tries to undo only that statement and keep the rest of your transaction, but it may have to roll back the whole transaction; check before you carry on.

Common causes

The disk is full, or the volume the database is on is: a small partition, a container’s writable layer, a phone or laptop low on space.
The temporary folder is full. Big sorts (ORDER BY or GROUP BY without a usable index), DISTINCT, UNION, temporary tables and VACUUM write temporary files. On a Mac or Linux they go to SQLITE_TMPDIR, TMPDIR, /var/tmp, /usr/tmp or /tmp, whichever is first writable, which may be a smaller volume than the database’s. VACUUM can need up to twice the database’s size free.
The WAL file grew. In WAL mode, changes collect in the -wal file until a checkpoint copies them into the database. A reader that never finishes stops checkpoints from completing, and the WAL grows without limit.
A page limit. PRAGMA max_page_count caps how many pages the database may have; once it’s reached, every write that needs a new page fails, even with plenty of disk.

How to fix it

See what’s using the space

df -h /path/to/folder "$TMPDIR"
ls -lh /path/to/folder/app.db*

A large -wal next to the database points at cause 3; a full temporary volume, at cause 2.

Free space, or move temporary files

Free space on the volume, or point SQLite’s temporary files at one with room before starting the program:

SQLITE_TMPDIR=/Volumes/Scratch/tmp ./your-program

PRAGMA temp_store = MEMORY; keeps temporary data in memory instead, if you have the RAM.

Shrink the file with VACUUM, or VACUUM INTO another disk

Deleted rows leave free pages inside the file; SQLite reuses them, but the file doesn’t get smaller. See how many there are:

PRAGMA page_size;
PRAGMA page_count;
PRAGMA freelist_count;

VACUUM rebuilds the file without them, but needs free space to do it. If the disk is nearly full, write the compacted copy to another volume instead, then swap it in while nothing has the database open:

VACUUM INTO '/Volumes/Other/app-compact.db';

Let the WAL checkpoint

Close long-running reads (a cursor never read to the end, a transaction left open), then:

PRAGMA wal_checkpoint(TRUNCATE);

It copies the WAL into the database and empties the -wal file once no reader needs it.

Raise or remove the page limit

max_page_count belongs to the connection that set it; it isn’t stored in the file. Find where your code or framework sets it, and raise it:

PRAGMA max_page_count = 1073741823;

Then check the transaction

After the error, run ROLLBACK if you’re inside a transaction, or check with your driver (in C, sqlite3_get_autocommit()) whether SQLite already rolled it back, and redo the work once there’s room.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0. Filling a real disk would affect other programs, so we capped the database at 20 pages of 4,096 bytes and inserted 200 KB:

sqlite3 full.db "PRAGMA max_page_count = 20; INSERT INTO logs (body) SELECT randomblob(1000) FROM generate_series(1, 200);"

Error: stepping, database or disk is full (13)

The shell adds “Error: stepping,” and the result code; SQLite’s message is database or disk is full. The insert was undone (logs had no rows), and a new connection read max_page_count as 1073741823, the default in this build: the cap hadn’t been saved in the file.

Inside a transaction, with one row inserted before the insert that failed:

Runtime error near line 4: database or disk is full (13)

Here SQLite kept the transaction: the COMMIT that followed succeeded, and the first row was saved. Through Python’s sqlite3 module (SQLite 3.53.4), the error was sqlite3.OperationalError: database or disk is full with sqlite_errorname SQLITE_FULL.

After filling a table to 15 pages and deleting every row, PRAGMA freelist_count was 13 and the file stayed at 60 KB; VACUUM brought it down to 2 pages, 8 KB.

In Inlet

Inlet shows SQLite’s message when a statement fails this way. The PRAGMA queries above run in a query tab, so you can see the page counts and free pages before you vacuum.

database or disk is full