Last weekend, on one of my production servers, we started seeing below errors in db2 diagnostic log (db2diag.log):
(1) The transaction log for the database is full
(2) MESSAGE : ADM1823E The active log is full and is held by application handle
Below are DB level configuration parameters that interest in long transactions:
db2 get db cfg for <Database> | egrep “LOGPRIMARY|LOGSECOND|NUM_LOG_SPAN|MAX_LOG”
Number of primary log files (LOGPRIMARY) = 240
Number of secondary log files (LOGSECOND) = 15
Percent max primary log space by transaction (MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
From above configuration parameters, below are various observations:
(1) In worst case, maximum number of LOG files that will be allocated are: 240 + 15 = 255
(2) There was a very old transaction/application started in log file:1 and spanned till log file:255, and still active. Unfortunately, this is long transaction is still looking for log space
(3) Transaction is still active and looking for next log file, but we have reached a limit of:255. So there is no way we can allocate 256th log file.
(1) Change LOGSECOND to -1 as below. This allows for infinite logging. In this case, an infinite number of LOGSECOND log files are allocated.
db2 “update db cfg for <Database> using LOGSECOND -1 IMMEDIATE”
(2) Kill the application that is doing this oldest long transaction. Below is a nice technote from IBM on this subject: