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

Hi,

this look for me like some design problem - long running transaction without commit or rollback.

try
gfix database_name -h -user sysdba -password masterkey
result should look like this
Oldest transaction 143527097
Oldest active 143527098
Oldest snapshot 143527098
Next transaction 143527099

if you have big difference between Oldest transaction and Oldest active then you have long running transaction problem

if you have FB2.1 or newer then you can do
SELECT *
FROM MON$TRANSACTIONS

to see how old your transaction is

Karol Bieniaszewski