Performing maintenance operations¶

SQLite databases need very little upkeep, but SQLite Expert gives you a few commands to keep them healthy and compact. All of them are available from the Database menu once you have selected a database in the tree panel, and the most common ones also appear as buttons on the Database tab.

While a command is running¶

Most of the commands on this page read or rewrite the whole database file, which on a large one is minutes rather than seconds. Each of them shows a progress bar with its percentage written inside it, the time it has been running, and a Stop button, and none of them holds the application still while it works.

Repair and Recover are the exceptions: they count the rows before they start, so their bars are true fractions. Everywhere else the progress bar is an estimate, and it is worth knowing where the estimate comes from. SQLite reports nothing about how far through a statement it is, so SQLite Expert measures each command against what the same command took last time, scaled to the size of the database in front of it. The first time you run a command the bar has only a rough figure to go on and may be some way out; from then on it learns from every run that finishes, and settles down after two or three. Where it has nothing at all to go on it moves rather than fills, which means "working, no estimate yet"; a bar in that state carries no percentage, because there is none to give.

How much work there is and how long it will take are worked out separately. The amount is what is remembered between runs; the speed is measured inside the run, over the last few seconds rather than the whole of it, so a database on a network drive or a shared folder — slow to begin with while nothing is cached, quick afterwards — is followed as it speeds up instead of being judged by its first seconds.

The time it offers is shown as about 2:30 remaining, and it appears only once there is enough of the run behind it to be worth quoting. It counts down no further than one second, and a run that outlasts its own estimate — a first run, usually — says longer than expected from there until it finishes, since at that point there is no honest number left to give.

Stop takes effect within a second, wherever the command has got to. Vacuum, Vacuum Into and Reindex All Tables are single statements that SQLite undoes as a whole, so a stopped one leaves the database exactly as it was.

Checking a database¶

Choose Database → Integrity Check to open the check dialog. It offers four independent checks, all of them on to begin with; clear the ones you do not want and press Start.

The Check database dialog

Integrity check — runs SQLite's PRAGMA integrity_check, which examines the whole file for out-of-order records, missing pages, malformed records and corrupt indexes.
Foreign key check — runs PRAGMA foreign_key_check, which reports rows whose foreign key points at a parent row that is not there, and foreign keys naming a parent table that does not exist at all.
Schema references (views and triggers) — finds views and triggers that name a table or a column which no longer exists. SQLite resolves those names when a statement is used, not when the object is created, so dropping a table or a column leaves the view or trigger sitting in the schema, perfectly readable and quietly broken. Nothing complains until someone selects from it or fires it.
Index consistency — compares every index against the table it indexes, in both directions: entries the index carries that no row backs, and rows the table holds that the index has missed. Where it finds a disagreement it names the row.

The bar covers the whole run rather than each check in turn, and carries the percentage of it that is done. The line beside it names the check that is running — Checking indexes — then how long the run has been going and how much longer it is expected to take.

It names the check rather than the table or index the check has reached. Naming the object made the line as wide as the longest name in the database, and what got pushed off the end of it was the remaining time, which is the part somebody watching a long run is waiting for. The Log tab still records every check by name as it starts and as it finishes.

The four answer different questions and none of them covers another — the two pragmas never look at what a view or a trigger means, and the schema sweep never reads a data page. Each check runs over every attached database, and everything found lands in a single list saying which check reported it, which object it concerns, and what is wrong. While a check is running, Start becomes Stop.

Tip

Run a check if a database has been copied from unreliable storage, or if you ever see unexpected results — it's a quick way to rule out file corruption.

About the index check¶

Index consistency is the slowest of the four, because it reads every index and every table in full — so on a large database it is worth starting, and abandoning if it takes longer than you want to wait. Stop ends whichever check is running, this one included: it interrupts the connection, so a pragma already inside the engine comes back rather than running to its end.

It is deliberately narrower than PRAGMA integrity_check: it checks indexes against their tables and nothing else — not page linkage, not cell ordering, not NOT NULL or CHECK constraints. What it adds is detail, because it reports the individual rows that disagree rather than a summary line. Use it alongside the integrity check, not instead of it.

Some indexes are reported as not checked, with the reason given. The usual cause is an index on a generated column: SQLite recomputes such a column from the table rather than reading it from the index, so comparing the two would compare the table with itself and pass everything. Rather than report a false pass, the check says it could not look.

Tip

If the index check reports problems, Database → Reindex All Tables rebuilds every index from the table data, which is the normal repair for an index that has drifted out of step with its table.

Vacuuming a database¶

Choose Database → Vacuum to run SQLite's VACUUM command. As you delete and update data, a database file accumulates unused (free) pages and its data becomes fragmented. VACUUM rebuilds the file from scratch, reclaiming that free space and storing the data contiguously, which can shrink the file and improve performance.

Rebuilding the file means reading all of it and writing all of it, so on a large database this takes a while; a window reports how far along it is and can stop it. When it finishes it says how long it took.

Vacuuming into a new file¶

Choose Database → Vacuum Into... to write a compacted copy of the database to a new file, leaving the original untouched. You are asked where to put the copy; if you pick a file that already exists, SQLite Expert replaces it.

This runs SQLite's VACUUM INTO command. It does the same work as Vacuum, but writes the result elsewhere instead of rewriting the file in place, which makes it useful in three situations:

taking a consistent, already-compacted copy of a database while it is still in use;
compacting a database you have opened read-only, which plain Vacuum cannot do;
compacting without needing free disk space beside the original file.

Note

A database opened through an encryption library may need extra care here: if the library reserves bytes on each page, the copy is written without that reservation and the library may not be able to open it. Use Database → Backup instead when you want a copy of an encrypted database.

Reindexing all tables¶

Choose Database → Reindex All Tables to rebuild every index in the database. This is occasionally useful after changing a collation sequence, or to rebuild indexes that may have been affected by corruption.

As with Vacuum, this reads every table and writes every index again, and reports its progress in a window you can stop.

Analyzing a database¶

Choose Database → Analyze to run SQLite's ANALYZE command. It reads the tables and their indexes and writes what it finds into a table called sqlite_stat1: how many rows each index covers, and how many rows a lookup on it typically returns.

The query planner uses those figures to choose between the indexes available to it. Without them it works from built-in guesses, which are reasonable for an average table and can be badly wrong for an unusual one — a column where almost every row holds the same value, for instance. If a query is slower than it looks like it should be, and its query plan shows SQLite choosing an index you would not have chosen, running Analyze is the first thing to try.

The statistics are a snapshot, not a live count. They do not update themselves as the data changes, so a table that has grown or shrunk a great deal since the last run is worth analyzing again.

Optimizing a database¶

Choose Database → Optimize to run SQLite's PRAGMA optimize. It does the same work as Analyze, but only for the tables that would actually benefit — those whose indexes have never been analyzed, and those whose size has changed tenfold since they last were. Everything else it leaves alone.

That makes it the one to run habitually. Analyze re-reads every table whatever state it is in, which on a large database is slow; Optimize usually finds there is nothing to do and returns at once. When it does find work, it reports the ANALYZE statements it ran, so you can see which tables it touched.

Reclaiming free pages incrementally¶

Choose Database → Incremental Vacuum to return the database's free pages to the file system without rewriting the file.

This command is only available on a database created with auto_vacuum set to incremental; on any other database it is greyed out. You can see and change the setting on the Database tab — but note that switching auto_vacuum on an existing database only takes effect after a full Vacuum.

Where it applies, it is the cheaper half of what Vacuum does. Vacuum rebuilds the whole file, which reclaims free space and defragments the data, but has to write the database out again from scratch. Incremental Vacuum only releases pages that are already free, leaving the rest of the file where it is. It is quick, it can be run while the database is in normal use, and it does not defragment anything.

SQLite Expert reports how many pages were returned.

Checkpointing the write-ahead log¶

A database in WAL mode does not write changes into the database file as they happen. They go into a separate write-ahead log — the -wal file beside the database — and are folded back into the database later, in an operation called a checkpoint. SQLite normally checkpoints on its own, but it will not do so while any connection is reading, so on a busy database the -wal file can grow and stay large.

Choose Database → Checkpoint WAL to run a checkpoint yourself. The submenu offers the four modes SQLite provides, in increasing order of how hard they try:

Mode	What it does
Passive	Writes back as much as it can without waiting for anybody. Never blocks, and never fails — it may simply do less.
Full	Waits for writers to finish, then writes the whole log back.
Restart	As Full, and additionally waits for readers, so that the next writer starts the log again from the beginning instead of appending to it.
Truncate	As Restart, and then shortens the `-wal` file to nothing.

Truncate is the one to choose if what you want is the -wal file gone from the disk; the others fold the data back but leave the file at the size it had reached.

SQLite Expert reports how much of the log was written back. If another connection held the checkpoint up, it says so, and how far it got before stopping — try again when the database is idle.

The whole submenu is greyed out on a database that is not in WAL mode. You can see and change the setting on the Database tab: it is the journal_mode row in the left-hand panel, and setting it to wal is stored in the database file, so it stays that way the next time you open it.

Warning

If the database is being replicated by a tool that reads the write-ahead log — Litestream, for example — a checkpoint you run yourself can disrupt it, and Restart and Truncate especially so. This is why SQLite Expert never checkpoints on its own: the command runs when you ask for it and at no other time.

Note

Analyze, Optimize, Incremental Vacuum and Checkpoint WAL act on the database selected in the list on the Database tab, which matters when you have attached more than one.

Repairing a database¶

If a database has become damaged, choose Database → Repair to open the repair dialog.

The Repair database dialog

SQLite Expert creates a new database and copies as much data as it can recover from the damaged one. The log shows what it is copying as it goes, and the bar beside Stop shows how far along it is: the records are counted before any are copied, so the bar is a true fraction rather than an estimate. A table too damaged to count is left out of the total, and the bar then waits at the far end rather than claiming to be finished.

Warning

Choose the option to repair into a new file so your original database is left untouched. Repair is a best-effort recovery — there is no guarantee that all data can be recovered, so always keep the original until you have verified the result.

Recovering a badly damaged database¶

Repair works through SQL: it reads the schema, replays it, and copies the rows out table by table. That needs a database SQLite can still open and a schema it still agrees to parse. When the damage goes deeper than that — when the file opens but every query answers database disk image is malformed — choose Database → Recover instead.

Recover reads the file page by page rather than through SQL, so it returns rows no query can reach. It can also sweep pages that belong to no surviving table into a lost-and-found table, which is data that would otherwise be lost outright.

The price is fidelity. Recover rebuilds the schema rather than copying it, so column order, constraints, triggers and views may come out different from the original, and anything it cannot make sense of is dropped. That is why both commands are here:

	Use it when
Repair	the schema is intact and only some rows are unreadable
Recover	the file is badly damaged and Repair cannot get through it

The options are:

Recover orphaned pages into table — collect records that cannot be attributed to any recovered table into a table of that name. Their column names are gone, so the rows arrive as plain values; worth having when the alternative is nothing.
Include pages that look free — also read pages that appear to be on the free list. Recovers more, but "recovers" deleted rows along with it. Only has an effect together with the option above.
Preserve rowid values — keep the original rowids instead of assigning new ones. On by default, and worth keeping on when other tables refer to those rowids.

Warning

Recover into a new file, and keep the damaged original until you have checked the result. As with Repair, this is a best-effort recovery: run an integrity check on what comes out, and compare row counts against what you expect.

Note

Recover needs the recover extension, which is built into the sqlite3.dll shipped with SQLite Expert. If you have pointed the application at a different SQLite library — an encryption build such as SEE or SQLCipher, for instance — the command is greyed out, because those libraries do not provide it. Repair and every other maintenance command still work.