 |
(Chris Eaton) Here is another log related monitor script. In this case we are looking for the oldest transaction that is holding log space. The amount of active log space used is determined by the oldest uncommitted transaction.
For example, if a transaction is uncommitted and it's log records are sitting in log number 5823 then DB2 will not remove that log file from the active log path until that transaction commits. If you have only enough space in your log directory for 50 log files then by the time you fill up log file 5873, if that oldest transaction has not committed, then you will have filled up your active log path and transactions will fail due to insufficient log space.
There are of course solutions to this problem like specifying infinite logging and/or specifying the num_log_span database configuration parameter (which indicates the number of log files a single transaction is allowed to span before that transaction is rolled back), or the max_log database parameter (which indicates the percentage of active log that any one transaction can use up before it is rolled back). But if you find you are using up too much active log (from my previous posting) then you can use this query to find out what user and application is holding up the tail of the active log. If this application were to commit, there would potentially be some log space freed up for other transactions to use.
If this transaction has been idle for some time then perhaps the person running the application has updated some rows but has stepped out for a coffee without committing their transaction. |
