Just to let you know we found the issue and solve it, it appears to be "a bug or glitch" that allowed us to add "not null" fields to a table after an existing SP was compiled to update and in some circumstances insert records into the same table. When re-compiling the SP we noticed the compiling error, and traced it back to the new fields on the table, however Firebird never complaint when we added the 2 fields as NOT NULL regarding the existing SP. I am not sure if Firebird runs a "validity check" when adding fields to a table regarding existing dependencies, perhaps it is a grey "philosophical" area as adding fields should not cause issues but in this case the DB was growing in size constantly until we re-compiled the SP. Let me remind you the SP was there to update the table, not to insert records (99.999% of the time).
Any-ways, the issue disappeared once we re-compiled the SP

Regards and thanks to all the people that assisted with the diagnosis,
Fabian

--- In firebird-support@yahoogroups.com, "fabianchocron" <fabianch@...> wrote:
>
> Hi All,
>
> We are experiencing a very strange behaviour with FB 2.1, we have a DB with just 1 table, and 1 SP to update the table. There are very few records on the table, and we run updates on it every minute. We noticed the DB size keep growing at about 10 to 20 mega per day. From memory in 1996 Microsoft SQL Server was performing SQL updates on tables by adding a new record with the changes and deleting the previous record. Assuming FB is doing the same, that explains why the DB is growing, however we are wondering if there is any way to set FB to "re-claim" deleted records and re-use that space. At the moment it seems FB keep allocating new disk space for each update and it is not re-using the free space containing the old updated records. The only way we can reduce the DB size is backing up the DB (taking it off line) and then restoring it on top. That works but takes the DB off-line for 2 minutes. If we do not do the backup and restore, the DB response degrades every day, to the point where it becomes too slow. We would not be surprised if we had many records been added to the DB every day, or if we were running a combination of deletion and inserts, however as previously explained all we do is update about 10 records every minute, and that is all the DB holds, there are no other records or tables in the DB.
>
> We would appreciate if you can give us a workaround, or a different approach to avoid the DB growing in size and consequent slow response.
>
> Regards
> Fabian
>