I am researching a problem in a program of a client of ours where the database keeps growing bigger while there is no data added to the database. I have learned that this occurs when there are a lot of open transactions that stay resident and prevent new transactions from cleaning up carbage data. So somewhere in the program there has to be a part where a transaction stays open.
The program runs 24/7 in a production environment so it grows very fast to around 12 mb and then the IBServer hangs. Only after performing a restore/backup the db file goes back to around 280kb and the IBServer runs again.
My question is. When do open transactions occur. I'm using Delphi as development tool. How can I trace a open transaction.
Also in IBConsole I use Maintenance->Statistics to get a list with info about the current transcations.
Currently it shows me:
Oldest transaction 19
Oldest active 20
Oldest snapshot 20
Next transaction 8261
What does this mean ? The difference between Oldest and Next transaction ?