Subject Problem with growing database
Author Sten Karlson
Hi all!

We're developping an application that will store measuredvalues from
a meassuring/control system that we are developing.

Our problem is that the database is constantly growing larger and
larger. Since the aplication will run unattended 24 hour per day,
every day, we have problems doing backup and restore.

Here is a short description of our system.

There is a single computer (running win 2000 and IB 6.01) attached
to the measuring system. 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.

DATA contains about 100 records, one for each parameter or data tag.
The data collecting application reads serial data and uppdates the
corresponding tag in the DATA table.

We also have a trigger (after UPDATE) on the DATA table. This
trigger also writes data to a trend table that stores
TAG number, Date & Time and the value.

We have some prestanda problems and we can't run procedures that
take long time and/or take much CPU power.

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 :(
And even if you do such thing, the size of the database doesn't
shrink a single byte!!!

We tried to use an table attached to an external file instead. But
the database grow alot anyway. Why????

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

Why is the database growing even if we only do UPDATE on one TABLE
and stores data in an external table?

Is it somthing I can so to eliminate the constant growth of the

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

Thanks in advace for every comment/suggestion
Sten Karlson