Subject Re: [firebird-support] Firebird 2.1 database growing in size on record updates
Author Thomas Steinmaurer
> 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.

How are you handling transactions in the client application?

Perhaps garbage is acummulating due to using some sort of commit
retaining etc ...

With regards,

Thomas Steinmaurer
Upscene Productions

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!