> > If applications are holding it open, perhaps some particular
> > window in an application.
> This should not be the case, since user stay on their app. all day long and
> sometime the problem can clear itself when load is slow down. It seems FB is
> too busy to clear up its garbage and it lock up itself -- I may be wrong.

This might suggest that the sweep is starting just when you
don't want it.

> > > 3 We set sweep interval = 20,000. Would it help if we set sweep
> interval to
> > > 3,000? Will the sweep process clear the OAT/Next Transaction gap?.
> >
> > Set it to 0 and run sweep at night when it's quiet. Then it
> > WONT happen just as you get busy <g>
> If we set it to 0, will the system slow down after a period of time? since
> the OAT gaps keeps growing.

I had a problem on one site where we got 50Mb growth in the
database per hour ( the database only had 5Mb of data! ). In
this instance the performance was not a problem, while it
was growing, that happened when you tried to clean up. It
was quicker to build a new blank in this case, rather than
wait for a sweep and a backup and restore. ( It was a clone
database on a slave machine ).
The point is that in normal operation, you don't need the
sweep, that just tidies up later, so as long as there is
enough disk space you will not have a problem.

> > > 4 The Application use IBO with AutoCommit transaction. If the explicit
> > > transaction is used it is always end with Commit or rollback. Can
> > > Rollback contribute to this problem?
> > Switching to IBO cleared all my BDE related transaction
> > problems, so it's unusual for something to stay open. I
> > would still suggest that may be the problem.
> Do you mean ROLLBACK can cause the problem? or open transaction is the
> problem? (which I try very hard to find in the app. any open transaction and
> still don't want to believe that the app. has some open transaction

Open transactions are the problem. Rollback may affect
things, but if other queries are only looking at Commited
data, then the rollback should be no more of a problem the
commit.
I have found that IBO is much more stable on OAT control, so
if your problem is not caused by the sweep getting in the
way ( and it does - setting to 200 it runs more often on
smaller chunks, but its still better overnight ) then
something must be manually holding a record in edit mode so
that it can't be finished.

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services