Subject Re: [firebird-support] Re: Database Size problem?? Please Help
Author Ann W. Harrison
Allen wrote:
> But is there any way I can shink the db(other than back and restore
> it) or force it to reuse the free space?

You can't shrink a database, but Firebird will reuse space that's been
released, if you can get the space released. Your database is growing
because Firebird keeps back versions of records that have been modified
or deleted until all transactions that could have seen the old version
have ended. Your pages are filling with old record versions and index
entries for old record versions.

The space that can be reused consists of data pages and index pages.
When a record version is removed from a data page, the space it took can
be reused by new data for the same table. If all entries on a page are
removed, the page is released and can be reused for any purpose.

A long lasting transaction will keep records and thus pages from being
released. Firebird releases old record versions are released when a new
transaction runs across them and recognizes that they are too old to be
of any value to any live transaction.

So, the first thing to do is look for long lasting transactions and
either stop them regularly, or make them read-only, read-committed.

The second thing to do is to insure that all old records are read from
time to time. The easiest way to do that is to run gbak, sending the
output to /dev/nul.