Subject Re: [firebird-support] database maintenance?
Author Alexandre Benson Smith
heineferreira wrote:
> Hi
> I come from a SQL Server background.
> Obviously on any database, even file-based ones like Access and Paradox
> you should perform daily backups. Do you have to perform any maintenance
> on a Firebird database? I know on Access databases you must do frequent
> compact and repair. On SQL Server you must do database integrity checks.
> You must also defrag the database internally. Also, on SQL Server you
> must rebuild your indexes from time to time. You have to do these things
> manually on SQL Server Express but you can schedule them on the paid
> versions of SQL Server. What must you do to maintain a Firebird database
> and how do you do it?
> Thanks
> Heine


Firebird is known to be a DBA-less database, so it should work pretty
well without intervention.

If your application is well designed you will help FB to perform well,
if your application is not so well designed you will have more trouble
and will need to perform some tasks at a given period.

Search the web and the firebird related sites for:
garbage collection
index statistics

The most common case of bad performance is a bad written app that keeps
the transaction open for a long period, and because of it a lot of
garbage are accumulated on the database, a stuck transaction prevents
the garbage collection process and the sweep process.

There is no magic rules, but I recommend you to perform a sweep (use
gfix for that) daily on a time where there is less database activity,
perform a back-up as often as possible and try to restore it to another
location to see if it's good, recalculate the index statistics from time
to time (here I could not say a good period, it will be influenced by
your usage pattern), when you have nothing more interesting to do in
your life perform a full back-up/restore cycle to recreate the database
from scratch and rebalance your index trees completely, note that the
last step is not required to be done so often (I have databases that I
don't do it for some years)

see you !

Alexandre Benson Smith
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil