Question & Answer
Transaction log space is full. What should I do ?
Transaction log space might be full due to few different reasons,
- Faulty application not committing frequently enough and holding large amount of log space back
- In-doubt transaction hanging around
- The total transaction log space is not configured big enough
The log full situation is experienced when the transaction log space utilization exceeds the configured log space size.
The log space size is determined by,
(Number of primary logs (LOGPRIMARY) + Number of secondary logs (LOGSECOND) ) x Log file size (LOGFILSIZ)
Following kind of messages might show up in the db2diag.log
2014-02-10-16.09.51.218677-300 E14706069E598 LEVEL: Error
PID : 8101 TID : 139796777264896PROC : db2sysc 0
INSTANCE: <inst-name> NODE : 000 DB : <db-name>
APPHDL : 0-7466 APPID: <app-id>
AUTHID : <auth-id>
EDUID : 104 EDUNAME: db2agent (<db-name>) 0
FUNCTION: DB2 UDB, data protection services, sqlpgResSpace, probe:2860
MESSAGE : ADM1823E The active log is full and is held by application
handle "<app-handle>". Terminate this application by COMMIT, ROLLBACK or
Whether the above kind of db2diag.log messages observed or not, to get immediate help identify the oldest transaction from that moment first, which should be the one holding the oldest transaction log most of the times.
The database snapshot will show the oldest transaction.
Collect following output,
db2 get snapshot for database on <db-name>
And, look for following in the database snapshot output,
"Appl id holding the oldest transaction "
The application handle showing up against the above field have to be forced off to get the transaction log space back against that.
db2 "force application (appl-handle)"
If there are more than one application which might be causing the issue then the same step might have to be repeated multiple times forcing the current oldest transaction from that moment, until the log full situation is taken care.
Following could be run during the time of the problem to get a full picture of all the transactions,
db2pd –db <db-name> -transactions
There are some rare situations when the transaction log files might be held by indoubt transaction.
Indoubt transaction can be caused by anything using two phase commit.
The way to check indoubt transaction is to run,
db2 list indoubt transactions with prompting
and, then take necessary step to rollback or, forget the transaction.
As a permanent solution, fix the queries which might not be committing frequently enough or, increase the total log space if it's not large enough by increasing any of LOGPRIMARY, LOGSECOND or, LOGFILSZ as necessary.
16 June 2018