Subject Re: [IBO] IBObject SavePoint method
Author Helen Borrie
At 12:39 AM 7/09/2005 +0000, you wrote:
>Hi there,
>SavePoint method does commit retaining, and I believe the old records
>created from SavePoint were prevented garbage collection, and so we
>are experiencing database size ballooning from our Delphi software.
>The worst case we had is from 23MB to 4GB in a few weeks time. Through
>IBConsole, we did the following to this ballooned database:
>1) a database sweep, but the database file size stays the same.

Yes. InterBase and Firebird never give back disk space to the operating
system. However, sweeping frees up the space that was occupied by the
garbage (old record versions) and the database engine will then re-use that

>2) a database backup/restore, and the database file size was reduced
>from 4GB to some MBs.

Restore from a gbak file literally creates a new database. The size of the
database file after a restore is the best indication of the "real" size of
your database. The difference in size between the old and the new database
gives you an approximate figure for the amount of space that is either
"spare" (free, and waiting for new record versions) or occupied by
garbage. When we see severe differences like yours, we know that the
application code is causing problems for the database.

>My questions are:
>i) We know a proper database design will be using transaction with
>explicit commit.

This is a question of application design, not database design.

>However, apart from re-design the software, and/or
>using IBConsole tools, does IBObject provides a way to garbage
>collect the old records created from SavePoint?

IBObjects itself does not have any service components. However, the
IBOAdmin components of Lorenzo Mengoni ( are made for use
with IBO and they work well. The TIBOValidationService component can be
used to set up a sweep programmatically.

However, service components do NOT work with InterBase 5.x and below.

>Are these old records remain interesting in the database?


>ii) Did the Sweep process clean up the old records that were prevented
>garbage collection from Savepoint.

Yes; as long as there are no transactions active in the system that will
prevent these old records from being cleaned up.

>In the Help document, it states
>that the Sweep process guarantees all records that are garbage
>collected, including those that were rolled back.

Not quite. Sweep cannot garbage-collect "interesting" garbage. It is your
application code (never performing hard commits) that prevents this garbage
from becoming "uninteresting".

>Are there any differences on old records created from Savepoint and Rollback?

There are differences between the way the obsolete record versions from
rollbacks and commits are handled, if record versions are in fact stored.