--- In firebird-support@yahoogroups.com, "innoy1k" <duncan.chen@t...>
wrote:

> Our client server Delphi software is currently using Interbase
> database with FB1.5 services. A couple of our new client's database
> growths from some MBs (e.g.: 23MB) to GBS (e.g.: 4GB) with very

little

> data compare to others. I've collected some statistic as shown in

the

> following:
>
> Number of users: 2
>
> Date: 29/04
> Database size: 4GB
> table Tfile count: 66
> table Lassign count: 193 (only one BLOB field)
> Action: Run Backup/Restore
> As a result of Backup/Restore proces, the database size was reduced
> to: 23MB
>
> Date: 06/09
> Database size: 2.483GB
> Number of transactions: 54,109
> table Tfile count: 231,
> table Lassign count: 1370 (only one BLOB field)
>
> The software does not run batch process. Does anyone know:
>
> 1) What could cause the problem?

Poor transaction management. Make sure you explicitly commit (not
commit retaining). The problem with MGA databases such as Firebird
and Interbase is also their advantage. Previous versions of records
are kept if there may be another transaction that could potentially
be interested in the old value. It does not take too many rogue
transactions to effectively stifle garbage collection.

>
> 2) I was suggested that it might be to do with the
> IBODatabase.Savepoint in our Delphi code, because SavePoint keeps

old

> state of the records and creates new version, and the old ones are

not

> garbage collected unless you run backup/restore. Is this true?

A savepoint doesn't create a record version, an insert, update or
delete does. Providing the transactions are well managed, there will
not be too many "back versions", and they can effectively be garbage
collected.

Garbage collection occurs either as a special thread in Superserver,
or by the poor bugger who stumbles across it in classic. Once the
record version is flagged as garbage, the space it occupies can be re-
used by another version of the record. This behaviour means that most
databases will expand rapidly after being created then hit some sort
of predicatable growth rate.

Now the database never hands back to the OS space, but it won't
request space for more pages if there is some available already which
have been garbage collected.

My best guess is that you have one (or possibly lots) of old
transactions that remain interesting. This is preventing the old
record versions from being garbage collected, and hence space from
being re-used.

Adam