Subject Re: [firebird-support] GDB file grows
Author Helen Borrie
At 12:12 PM 24/09/2004 +0200, you wrote:

>Hi all,
>We have a firebird database 1.0.3 Version WI-V6.2.972 installed since some
>months. Yesterday we detected that the .gdb file was huge -18Gb-. When
>doing a backup of such huge database with gbak, the .gbk file was about 20
>Mb -normal size-. And restoring the .gbk file we got a normal size .gdb
>file -about 22Mb-.
>So, we delete the huge file and start working with the restore one. After
>a while the size was 30Mb, after another while 50Mb, ... it seems that
>something makes the .gdb file to grow, and it is not because such volume
>of data is stored in the database.
>When we did again the gbak to the, at that point, 150M gdb file, we got
>again a normal size .gbk file and the restored one had also normal size.
>Has anyone experience something like this? Any help is really appreciate.

My guess is that you have this combination of conditions:
read-write transactions running that never finish, the sweep interval set
to 0 (auto-sweeping is disabled) and you never do manual sweeps. The first
is a problem with your application code; the other two are caused by
administrative ignorance and neglect, but the three are intertwined.

You can switch off auto-sweeping if your applications manage transactions
well (since auto-sweeping is unlikely to be triggered off where the
application code takes proper care of transactions). If you switch off
sweeping, you must do manual sweeps. With good transaction management, the
need to sweep will be perhaps monthly, quarterly or even yearly. How often
it's needed in a well-behaving database would depend on how much deleting
you do. Apart from restores, sweeping is the only way to free up space
that was occupied by deleted records.

With poor transaction management (which your symptoms suggest) you might
need to sweep several times a week. But you really need to hunt out those
perpetual transactions and fix them so that you avoid getting the database
into this state.

Another contributor to your troubles may be a page size that is too
small. If you inherited a database that was created with the IB 5 or 6
defaults, or an early beta of Firebird 1, the default page size of 1 Kb is
pretty unrealistic and will cause to server to waste space and to draw
blocks of page storage too often.

You can find out quite a lot about the state of the database by running a
gstat -h on it. Pipe the output to a file and paste it here so we can help
to work out what's going on it.