At 04:28 AM 10/15/2004, Faisal Abdullah wrote:

>Hi, is there a way to vacuum or reorganise tables? The only way I know
>of doing it is to do a backup restore? But this off course requires me
>to take the database offline.

Firebird has two mechanisms for reclaiming space on data pages,
garbage collection and sweeping. Both are done with the database
live.

Garbage collection is done automatically and continuously.
It removes old record versions, changes that rolled back, and
deleted records. Generally, a garbage collect pass just
releases space on a page that will be used for new records
added to the table the page "belongs" to. If garbage collection
removes the last record on a page, that page is added to the
free page list and can be reused for any database object -
data, index, space management, etc.

Sweep is a special variant of garbage collect that does all the
things that garbage collect does plus one. Because a sweep
starts at the beginning of the database and visits all data pages,
it can change the state of old transactions from rolled back
to committed once it has removed all changes made by those
transactions. Changing the state of an old transaction is
important because rolled back transactions are "interesting" -
meaning that an active transaction must be aware of their state
and prepared to ignore and possibly clean up their work.
Reducing the number of "interesting" transactions reduces then
number of checks that must be done to validate a record and
makes the system faster.

Running a sweep puts some load on the system so sweeping is
often scheduled for a slow time, just like database backups.

Regards,


Ann