At 04:56 AM 31/07/2007, you wrote:

>Hi,
>
>Newbie question:
>I added a large number of records to a db making it's file size 20mb.
>Then I delete all records, and size remains unchanged.

This is normal. The data in a Firebird database are not stored in
"individual files" as they are in ISAM-style databases. The engine
requests "pages" - chunks of disk storage - as required and manages
the internal structure of the database itself. It never gives back space.

>How does one therefore remove the stale records?

The database engine removes obsolete record versions itself, using a
process known as "garbage collection". Once that happens, the space
becomes available for re-use.

>I need to achieve this via code, so is there a sql statement that can
>do this or delphi component? (I'm using IBX atm. I don't mind using
>other components but need them to be free).

There is no way to alter the size of a database file, inside or
outside of application code, other than to take a gbak backup, then
do a gbak restore, then, if the restore is healthy, delete the old
database file and start afresh with the new one. (gbak can also
restore by overwriting the database directly, but this is NOT
recommended!). You can use the IBX service components to do backups
and restores from your application.

Don't worry about the database file growth. It will grow fast at the
beginning of its life but, once you're into a pattern of maintenance,
with well-behaved application code, it won't grow any faster than the
growth of actual data.

It's rather common for Delphi applications, particularly IBX ones, to
be written in such a way that garbage collection is seriously
inhibited. In those cases, exorbitant database growth can be
observed and performance will degrade progressively. To some extent
you can housekeep such sick databases by taking out the users
periodically and running a Sweep to free up some space. (Sweep is
also available via a service component.) However, if you are new to
Firebird, you really do owe it to yourself to get your head around
the effects the various properties of components, especially the
default transaction settings of IBX.

Also be aware that IBX is designed to support InterBase and is
[deliberately] not "Firebird-aware". It's uncertain at the moment
whether IBX is even usable with Firebird 2 and higher...

./heLen