--- In [email protected], "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
