> 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.

Additionally, I would take a step back and re-think transaction
management in your client applications in general.

According to your header page statistics, you had > 50 mio transactions
in 7 days, which means in your example approx. 83 transactions / second
(if my math doesn't fail *g*) in case of an 24/7 environment.

The transaction ID is a 32-bit integer, thus you can have ~ 2 bio.
transactions until a backup/restore of your database is needed, which is
~ 280 days after your last restore.

Keep an eye on that.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

> Regards,
> Wim
>
> --- In firebird-support@yahoogroups.com, 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
>>
>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>