OPTIMIZE TABLE like functionality for Firebird ?

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 ?
LVL 1
jjthomas3Asked:
Who is Participating?
 
Nick UpsonPrincipal Operations EngineerCommented:
firebird will reuse space from deleted rows once there are no processes that still consider them 'interesting'
a backup/restore will normally use less space than the original due to general compaction but a database should read a 'stead-state' where, except for additions to perhaps an audit table or new records, the size remains the same or grows slowly as more data is held.

Your description is a classic one of a database that has one or more uncommited transactions hanging around. At least a week after it's restore please post the output from gstat (just the header stuff for now)

please also post your backup command line as you may be able to add garbage-collection to it for some improvement.

Equivalent to Optimise table - what does that actuall do?
0
 
jjthomas3Author Commented:
NickUpson,

 Thank you for your response. I was, unfortunately, out sick... but will be able to post the requested GSTAT output and our backup command-line monday morning when I return to work.

As far as Optimize Table, I always assumed it cleared out "white" space or performed some level of clean up. Unfortunately I'm not too experienced with Database servers, but we've lost our DB Admin so I have to step up until a new one is found.
0
 
Nick UpsonPrincipal Operations EngineerCommented:
if that's what it does then garbage collection is the equivalent but it's on a database basis
0
 
jjthomas3Author Commented:
Based off of NiclUpson's suggestions I went back to the programmers and the DBAs. We found that there was a bug in the Firebird ODBC Drivers that was causing some of our queries to not "Auto-commit". This was causing upwards of 200,000 transactions to remain interesting to the system. This in turn was causing garbage collection to be essentially pointless.

Thank you for you time
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.