What does compact and repair do in a database?

Compact and repair is a health check and a tidy-up for a database file. In Base by Zaweba, Database > Compact and repair first makes a backup copy, then runs an integrity check, fixes records that point at records which no longer exist, checks and corrects Base's own design tables, rebuilds the indexes and compacts the file. You end up with a smaller, cleaner file that you know is sound.

Access users will know the name. The idea is the same, though what Base checks is specific to how it stores things.

Why a database needs it

A database file isn't rewritten from scratch every time you save. When records are deleted, the space they used is kept inside the file for later rather than handed back, so a database that has had lots of records added and removed can be bigger than its contents. Indexes, which make searching and sorting quick, can also become less tidy over time.

Then there are links. A lookup field in one table points at a record in another. If, somehow, the record it points at is gone, the link is broken. Base's delete rules are there to stop that happening in normal use, but an old file, an interrupted session or a hand edit can still leave a few loose ends.

What it does, step by step

It starts with a backup copy, so the file as it was is always there to go back to.

Next comes SQLite's own integrity check. A .obase file is a SQLite database, and this check looks at the file's internal structure.

Then Base looks for records pointing at records that have gone. Where the lookup is optional, it is simply emptied. Where the lookup is required, Base lists the records instead of guessing, because only you know what they should point at.

After that it checks Base's own design tables: the stored relationships, the diagram, forms, buttons and the start-up form. These are kept inside the same file, and anything out of step is put right.

Finally it rebuilds the indexes and compacts the file, handing back the unused space.

When to run it

There is no need to run it every day. Good moments are after a big clear-out (deleting a lot of old records, say), before sending a database to someone else, after something odd has happened such as the PC losing power mid-session, or when a database just seems slower or larger than it should be.

It is also a sensible first step if a form or the diagram behaves strangely, because those designs live in the tables it checks.

Reading the result

If everything was fine, you get a smaller file and nothing else to do. If Base lists records with a required lookup that points nowhere, open the table in the datasheet, find those records and choose the right customer, product or member for each, or delete them if they are leftovers. Running compact and repair again afterwards should come back clean.

How it fits with backups

Compact and repair isn't a substitute for backups, and it can't bring back records that were deleted. Base by Zaweba already copies the database once a day when you open it, keeping the latest ten, and File > Back up now makes a copy on demand. If a repair ever turns up something you don't like, those copies, plus the one made just before the repair, are where you go.