Thank you, for all of your answers.

Adam was right - the gap of almost 1900, which my gstat tool reported, was due to an open connection made by IBExpert during my test-run. Once I closed IBExpert, the gap OAT and Next Transaction came back to normal and was always between 1 and 2...

Helen mentioned, that it is not recommed to delete records table-wide. My trigger only deletes records in its own table, but due to foreign key (delete cascade) constraints, connected records will deleted too. I guess even this scenario is not the right one, so I have to redesign my trigger anyways.

But even if I limit my scenario to one table and created a simple trigger BEFORE INSERT like this:

[...]
BEGIN
DELETE FROM TESTTABLE WHERE CREATETIME < cast( 'now' as TimeStamp );
END

and f I keep inserting records in this TESTTABLE with a CREATETIME from now, all records will immediatly be deleted (or to keep it in other words: will get marked as deleted, thus take more space).

My INSERT statements are commited each time, but still - the database size does not stay constant. So after reading through all of your answers, my question is a bit different (I probably still don't understand this mechanism right):

Ann said that one way to remove deleted records is that the transaction that performed the delete needs to be commited. So is my Trigger commiting its command automatically, or do I need to add a COMMIT WORK in the second line?

Or am I forced to perform a SELECT on my deleted records, to free up the space for new records? Why is a sweep not enough to do this?

I am aware that my database won't shrink unless a backup and restore and without the space will never get back to the filesystem. I just want to keep my database at once size at a certain point of time by deleting old records using a trigger.

Thanks for your great help!

Jörn



-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]On Behalf Of Ann W. Harrison
Sent: Donnerstag, 21. September 2006 16:47
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Database Size


Priebe, Joern (GE Indust, Security) wrote:

> Hello Folks,
>
> I have (hopefully) an easy question concerning a database's size.

The gstat tool will report on actual space utilization including
back versions. The report will be long, but it will tell you
whether you've got a lot of partially used pages, or retained
back versions.

One thing to note is that your deletes won't actually release
space until you read those records, either in your application
or interactively with a select whose criteria could include them,
or backup the database, or sweep the database.

The delete marks the records as deleted - a deleted record takes
more space than an undeleted record. It is removed only after

1) the transaction that performed the delete commits
and
2) all transactions concurrent with that transaction complete
and
3) some transaction attempts to read the record

Gbak reads all records so it can perform the third action, as
can sweep.

Regards,


Ann


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links