We are running IB6.0 on Linux (Classic Server). Our database has 79
tables, 79 primary key constraints, 127 foreign key constraints, and 13
other indexes. We use "pure" SQL so we have no triggers, procedures or any
other non-portable constructs. Neither do we explicitly code plans, which
makes query optimisations a nightmare!

We use Java clients using Interclient 1.6, so our database is Dialect 1.

The Production system was started at the beginning of December, 2000 and
the database size is currently 1.8Gb. About five or six tables have more
than 1,000,000 rows.

The database updates consist mostly of inserts, but there are also a
significant number of updates which, of course, change foreign key fields
and alternate index fields.

We have automatic sweeping turned off and manually run sweep every night.

Occasionally, the database suddenly seems to hang. The first time it
happened, one of our jobs that joins a couple of the very large tables to
extract the previous day's data hung. Normally, it would take about 10
seconds to run. This time we killed it after an hour or so. A sweep did
not fix it. In the end, we had to back up the database (without garbage
collection) and restore it. The first restore attempt failed with a data
corruption (that was scary) but it succeeded the second time (anyone know
why?).

I have just read that large delete operations mixed with duplicate index
values can kill the garbage collector. Would the updates have the same
effect?

Are there practical steps that we can take to prevent these extremely scary
hanging events from occurring?

--
Adrian