FIREBIRD Questions and Answers

How to detect applications and users that hold transactions open too long?

To do this, you need Firebird 2.1 or a higher version. First, run gstat tool (from your Firebird installation’s bin directory), and you’ll get an output like this:
gstat -h faqs.gdb
Database “faqs.gdb”
Database header page information:
Flags 0
Checksum 12345
Generation 919
Page size 4096
ODS version Ii .1
Oldest transaction 812
Oldest active 813
Oldest snapshot 813
Next transaction 814
Now, connect to that database and query the MON$TRANSACTTONS table to get the
MON$ATTACHMENT_ID for that transaction, and then query the
MONSATTACHMENTS table to get the user name, application name, 1P address and
even PID on the client machine. We are looking for the oldest active transaction, so in this case, a query would look like:
SELECT ma.*
FROM MON$ATTACHMENTS ma
join MON$TRANSACTIONS mt
on ma.MON$ATTACHMENT ID – mt.MONSATTACHMENTID
where mt.MONSTRANSACTION_ID = 813,