Subject Re: [firebird-support] GDB file grows
Author Svein Erling Tysvaer
What you describe is a typical symptom of one or more long-running
transactions. Your statistics doesn't reveal much, but I guess this is
the statistics from a "fixed" database rather than the 150Mb database?

When Firebird deletes a record, it does so by flagging it deleted - not
physically delete the record. It does so because the deleted record
still may be visible for transactions that started before the delete was
done. And something similar is done for updated records, for old
transactions it may be older versions of the record that is visible and
not the latest version that contains the current data.

Make sure ALL transactions are properly committed (or rolled back) and
your problem will disappear. Typically, when you take proper care of
your transactions, the database file size will first increase a bit, but
then stop growing.


Trinidad Miravet Celades wrote:
> Hi all,
> I have a problem with a database that grows in a very strange way. In a
> normal state the database file size is 17Mb. In normal conditions the
> database grows approx. 30Mb in a whole year. In the actual state after
> three days of working the size of the database grew from 17Mb to 150Mb.
> After fixing and sweeping the database file size is again aprox. 17Mb,
> but again in two or three days the size is 150Mb and keeps growing very
> fast if you dont do a fix and sweep. Of course, that volume of data were
> not inserted in the database, let's say that the database was inflated
> but hollow inside.
> I am almost sure that the origin of all this is a massive data deletion
> -around 50000 records were deleted from one table-. But it is supposed
> that if after a massive data deletion you do a sweep of the database
> everything should be ok, isn't?
> The only way I have found to get back to a normal situation is create a
> new database, migrate the data to the new database and then everything
> is ok.
> If anyone could help me to confirm if the deletion is really the caused
> of this or how could I avoid this situation, I would appreciate too much.
> The result of gstat after the sweep is attached.
> I can attach too a gbak of the database is necessary.
> I am using Firebird 1.5
> Thanks for your help,
> Trini.
> ----------
> Database "grader.gdb"
> Database header page information:
> Flags 0
> Checksum 12345
> Generation 28
> Page size 4096
> ODS version 10.1
> Oldest transaction 19
> Oldest active 2
> Oldest snapshot 1
> Next transaction 21
> Bumped transaction 1
> Sequence number 0
> Next attachment ID 0
> Implementation ID 16
> Shadow count 0
> Page buffers 0
> Next header page 0
> Database dialect 3
> Creation date Aug 13, 2007 15:58:57
> Attributes force write