--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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.
>
> ./heLen

Here is the result of gstat. I did it this morning. The size of .gdb
file was 2GB, last friday was only 20M

-----------------------------------------------------------

Database "grader.gdb"

Database header page information:
Flags 0
Checksum 12345
Generation 1151
Page size 4096
ODS version 10.0
Oldest transaction 1079
Oldest active 1080
Oldest snapshot 1042
Next transaction 1145
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 Sep 25, 2004 8:26:23
Attributes

Variable header data:
Sweep interval: 20000
*END*

----------------------------------------------------------------------

In your mail you mentioned the need of sweep to free up space
occupied by deleted record. Well, some days before the problem was
detected, there was a deletion of around 67000 record in a table.
Usually no deletions are done in the database, they are done only to
clean historical logs. I think, that was the cause of the problem.
The system is running about 1 year ago and never detected something
similar before. So, this morning I did a manually sweep, it took
around 4 minutes -in a test database was only a few seconds- and
after that, it seemed that the database was not growing.

Do you think I should change the sweep interval, or just keep that in
mind and do manually sweeps from time to time?

Thanks for your help,

Trini.