Subject Re: [ib-support] Problem with growing database
Author Svein Erling Tysvaer
Hej Sten!

>There is a single computer (running win 2000 and IB 6.01) attached
>to the measuring system.

IB 6.01 - that's the pre-fork version! Firebird is a much improved (and
still free) version of that open source version, and Borland do sell newer
versions of Interbase, both of which are improvements of IB 6.01. Still,
your problem is not associated with this.

>On this computer we run two applications.
>One that collect data through the serial port and stores data in a
>data table called DATA. The other application is a user interface
>and it reads parameters and data values from the DATA table.

Both of these applications could be the source of your problems. You need
to make sure that all transactions are committed occationally, otherwise IB
will keep old versions of all records (both updated and deleted records) in
case they should be required.

>The problem is that we must remove old data from the trend table. We
>are supposed to store data for a week which means about 4-6 million
>rows. I haven't been able to write such question to database (delete
>everything that is older than a week) that dosn't take very long
>time and use a lot of CPU :(

Well, stop thinking like a Swede and become a CPU! CPUs don't get bored of
repetitive tasks. If you provide the correct indexes, why don't you run
that delete job e.g. every hour? I.e. delete everything that is older than
one week every hour.

>And even if you do such thing, the size of the database doesn't
>shrink a single byte!!!

As others have said, it never does. But if you take care of your
transactions it should reuse the space so that your system stops growing
after a while (in your case, after about one week ;o). This is one area
where I know people have had problems with the BDE which didn't stop until
the BDE was replaced by IBO (, but you haven't told us
what tools you use with IB6.01.

>Is there any one that have done something similar and solved this
>problem? I guess that all logger applications have this problem.

I think your problem has to do with transactions and not the kind of

>I'm getting a bit scarred. The hole project is relying on the
>database concept and I must make it work some how.

Well, I wouldn't be too scared. I've never used IB 6.01 - we went straight
from IB 5.6 to Fb 0.9.4 (Fb 0.9.4 was basically a fixed version of the open
source IB 6.01) for our production database, but providing you use the
right tools the most common reason for uncontrollable database growth is
programming mistakes relating to transactions. Many people using IB or Fb
wouldn't tolerate a fault in the engine like this, and at least in Fb it
would be fixed pretty quickly if an error was discovered.