At 08:39 PM 21/09/2006, you wrote:

>Hello Folks,
>
>I have (hopefully) an easy question concerning a database's size.
>
>To make it simple: I have a Firebird Database (Pagesize 4096) with
>some Tables all linked by a foreign key to a Main-Table. On the
>Main-Table I created a Trigger "BEFORE INSERT" which deletes all
>records older than a certain time. So after after inserting records
>at a certain time, this trigger deletes all obsolete records.

This is not recommended! A trigger is specific to the record being
operated on. It should never be used to do table-wide operations.

>I checked the Database with my DB Tool IBExpert and it shows me that
>the count of total records in the whole Database keeps constant
>after a certain time, which proofs that the trigger works fine.

Until you kick in some concurrent users.

>But I am very confused that the Database-Size (and allocated
>Pagesizes) stills increases - even after a long time where the
>trigger was working. Only a Backup/Restore shrinks it to a lower
>(expected) size. I know that the physical size of my database won't
>lower unless I start a backup and restore, but why are the pages
>with deleted records are "refilled" again? (Or if it so, how can I check it?)

Records are stored on pages. When deleted records are (eventually)
removed and obsolete versions of updated records are cleared out
(eventually) the vacated space is recycled. Space is *never*
returned to the filesystem. So, provided you are taking good care of
housekeeping, your database file size will settle down at some
point. If your data content is fairly stable and you are not seeing
file growth settle into a steady, stable range of size, it's a sign
that you need to pay more attention to housekeeping.

>My Sweepcount is 20000 and I am not using any BLOBS fields if this
>may be from interest.

It's not.

>I hope you can help me out or can give me some thoughts about this
>problem, because I need to keep my Database-Size constantly.

If you have a genuine need to limit the size of the database file to
some arithmetical projection of record count and record size, then
you shouldn't be trying to use a DBMS that uses multi-generational
architecture. Deleted and replaced records stay in the file until
housekeeping - garbage collection and sweeping - releases
them. Then, their space becomes available for storing more
records. If you don't look after the housekeeping, the engine will
need to ask the file system for more space, causing your database file to grow.

./heLen