Link to home
Start Free TrialLog in
Avatar of JRogerPanton

asked on

OPTIMIZE TABLE like functionality for Firebird ? how to clean up uncommited transactions

We have a database that continually grows and in the process, sufferers a performance hit once it gets a little big. We found that every 3 or 4 weeks there is a pressing need to stop the database, run GBAK to perform a backup/restore. This shrinks the database quit a bit and greatly improves our performance. Is there a way to manually perform the same functions as QBAK, through stored procedures or SQL calls to do the same job, but with out having to stop our database ? We've already verified that we're properly committing everything, and garbage collections are running, this is just not enough on its own.

One of our tables has a extremely high rate of record deletions, does firebird have an equivalent to Optimize table ?

If the case is you have 200,000 uncommited transactions how can you get these either to commit or how are you able to remove them, we have  a fb 1.5.3 database that has doubled in size it looks as though this may be the problem.

Avatar of rjgonzale

You can:

To commit or rollback
gfix -commit all <db>
gfix -rollback all <db>

To manually run garbage collector if required
gfix -sweep <db>

I hope that helps you
Avatar of JRogerPanton


We backedup and restored the database would this have had the same effect as these.
I didnt make any test about that, I've used firebird in production but never had to do a manual sweep.

But taking out transactions from limbo yes, it makes a difference....and you dont have downtime
Avatar of Nick Upson
The real solution is to find why you have all those uncommited transactions and make code changes in the application so that they are either commited or rolledback.
The problem is I really did want to avoid changing the code the system has been in operation a while and not exhibited this problem before.
That is the only way to solve it, you might be able to tell people to change how they use the application to avoid it, one or more people must have changed how they use it in order for this to appear. first problem is to locate where the problem is
I agree with Nick on this. This kind of thing doesn't appear unless long-running transactions aren't correctly committed.
Avatar of Delphian

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial