Subject Re: Why not sweeping?
Author fdt4y
Again, thank you guys - I've found the issue in a application that started a "read-only" transaction to update some labels on a form and keeping the OAT stuck.

With my luck, I closed the app and froze the poor server, now trying to sweep 7 days worth of transactions from all other tables... ouch - I was not popular. All in all, I made some program changes, the database is back up and running and the OAT seems to be moving forward slowly as it should.

What a great day this is - this has been troubling us for a while now and we could never pin point the problem.


--- In, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
> >Thank you very much for your responses - this makes sense. We have a couple of server side applications that run
> >constantly. Although, they should all be closing their connections. We will investigate further.
> >
> >Could this somehow be related to the .NET driver with connection pooling? The one application that I suspect
> >starts and commits small incremental transactions, but uses connection pooling?
> A common reason would be using CommitRetaining rather than Commit. Although CommitRetaining can be convenient, it stops the OAT from moving on so you must ascertain that a hard Commit is used occasionally. Connection pooling by itself shouldn't cause your problems, unless there's also some kind of "transaction pooling" (but a transaction can span several connections). Note that the transaction does not have to do any modification to the database, even SELECTs can make the OAT stuck (the one exception is that transactions that are read only AND read committed do not stop the OAT from moving).
> Actually, we had a similar (though not related) problem recently, which turned out to be due to having a TIB_Transaction (IB Objects) with AutoCommit, and then in code do 'if TIB_Transaction1.TransactionIsActive then TIB_Transaction1.Commit'. Due to the AutoCommit, TransactionIsActive was false, so no hard Commit was done. AutoCommit did CommitRetaining, so the OAT got stuck until the program terminated. Our users typically start that program in the morning and closes it at the end of the day, so we only had one day of delay. Your gap indicates that your problematic transaction (well, there could be several problematic transactions, you'll find out) was started shortly after the restore, so programs started later than about July 4th are not suspected.
> Set