We have a customer using Firebird 1.5.5.4926 Classic Server for a head
office database of approximately 3GB size.
During a typical day the database will have somewhere in the region of
100 active connections as the customers other sites insert/edit
information on this head office database.

Recently we have identified what appears to be some index corruption.

We have a query, similar to the following:

Select * from trans_table where seqid is null

(The field seqid on trans_table is a foreign key and has a referential
constraint against the seq_table).

We use the query to extract records from the trans_table that we need
to process further. The results of the query are processed as a batch
and the seqid on the trans_table is updated with a numeric value (the
seqid) that identifies the batch and marks that the records have been
processed.

What we have found is a number of cases where the query does not
retrieve all the records.
If we look directly at the table using IBOConsole, we can see records
with the seqid field shown as a null but the query just does not
return the records.

We have demonstrated that a backup and restore will resolve the issue
but we are extremely restricted in the number of times and the amount
of time we can take the database out of service.

Our question is, is there any way to rebuild the index that does not
require exclusive control of the database?

Failing, that does anyone with experience of this size of database
have any suggestions about the regularity with which we should do a
back up and restore?

Thank you for your assistance
Martin.