Subject Re: Database garbage collection
Author Adam
--- In, Rik Barker <rik.barker@...>
> Hi,
> It's just occurred to me that when using embedded server, if our app
> running, the database is unlikely to ever perform a pack (by which I
> remove any deleted records - is that called sweep now?)

Pack and sweep are two different things. When Firebird does its
garbage collection, the space the old record versions used to take up
goes into a slush fund to be reused as required.

To re-organise the data in such a manner that it is physically
touching each other so the file length can be shortened is an
extremely expensive operation. A backup-then-restore will achieve this
much much faster.

> The app I'm working on might well do 100,000 deletes and 100,000
> every couple of days - I want to ensure I keep the database well
> tidied. Is there a way to instigate database administration tasks
> API calls? Is it even necessary?

Certainly packing is pretty useless, unless
1) You are running out of disk space AND
2) You are confident you will never need that much space again

Garbage collection is essential for your database to run smoothly. As
is making sure the statistics for each index is up to date. Balancing
the indices may also be beneficial.

I would run a frequent gbak. This has the double benefit of doing
garbage collection as it goes. Alternatively, you could use the
services manager to do the same thing from within your application.

Backup-then-restore about once a year is useful.