At 02:40 AM 14/08/2007, you 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?

What do you mean by "OK"? Is it your belief that sweeping causes the
server to return space to the file system? If so, your belief is
wrong. Space is never returned to the filesystem. The reason why a
restored backup returns to its smaller size is that restoring totally
recreates the database and each data page is optimally filled.

Sweeping will be able to *free up* space from those deleted records,
provided there are no "interesting transactions" that are still
associated with them. "Free up" means that the space from those
obsolete records becomes available for re-use.

You state that "the database grows by 70 Mb in the whole
year". However, that is irrelevant to the size of the database file
if your application workflows, or your maintenance practices, or
both, are forcing the DB engine to request new pages constantly from
the filesystem.

Updates also create obsolete records, so you have garbage being
created from both deletes and updates. If your programming and
operational practices prevent this garbage from getting properly
tagged for releasing, that space remains occupied and, each time the
engine needs to write a new record version, it has to get more space
from the filesystem.

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

I hope you now understand why your database file doesn't get
smaller. You have some work to do, to find out what your application
code is doing that is preventing the normal garbage collection
process from doing its job of finding obsolete records, tagging them,
and subsequently freeing up the space they occupy. Typical causes
of/contributors to this problem are.-

-- application code written in Delphi using default property values
that were (foolishly) designed to make the programmer think that all
database systems work like Paradox or Access so that the programmer
does not take proper care of making timely hard commits to transactions

-- a database page size that is too small or too big

-- applications or environments that allow users to crash out of
applications (or abandon them) without taking care of dead transactions

-- plus a few more - but these will do for now. ;-)

> The result of gstat after the sweep is attached.

Those statistics have been taken from an almost new restoration of
the database and are not going to tell you anything. Next time the
database seems to start growing too much, take a gstat -h *before*
any sweep. That will give you a picture of how efficiently your
practices are taking care of garbage collection.

>I can attach too a gbak of the database is necessary.

Actually, you can't send attachments to the list. But we don't need
your database, only some useful statistics. Indeed, if you sent in
this "problem" in the belief that GC and sweeping are meant to make
database files get smaller, then you already have most of the
answer: database files don't get smaller, ever.

./heLen