Subject [firebird-support] Re: Why not sweeping?
Author Svein Erling Tysvær
>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.