[Db2] How to solve transaction log for the databases is full (SQL0964C)

You have three options to be relieved from the situation: (1) Commit or Rollback oldest uncommitted transaction, (2) Increase log area, or (3) use Advanced Log Space Management (ALSM).

1. If there is one (or more) long-running uncommitted transaction

As a quick workaround, specify the transaction causing "transaction log is full" situation, then commit or rollback it.

1.1. Determine the oldest uncommitted transaction.

Search ADM1823E message in db2diag.log file, then find application handle number in it.

 2010-06-10-12.51.38.458559+540 E1722296A538 LEVEL: Error
 PID  : 4391056 TID  : 1 PROC : db2agent (SAMPLE) 0
 INSTANCE: db2inst1 NODE : 000 DB  : SAMPLE
 APPHDL  : 0-123 APPID: *LOCAL.db2inst1.100610035141
 AUTHID  : DB2INST1 FUNCTION: DB2 UDB, data protection services, sqlpgResSpace, probe:2860
 MESSAGE : ADM1823E The active log is full and is held by application handl  "504".
           Terminate this application by COMMIT, ROLLBACK or FORCE  APPLICATION.

In this case, "504" is the application handle of the oldest uncommitted transaction. You can see the partition number and application handle as a set like "0-504". If you can see ADM1823E message many times, and they contain the same application handle continuously, it must be a cause of "transaction log is full" situation.

Note:
If the application handle number in the APPHDL field and in message ADM1823E are the same, it indicates that the application itself produces huge transaction logs and has caused "transaction log is full". You can limit the running time or the amount of log to prevent "transaction log is full", by using the num_log_span or max_log database configuration parameters. Refer to the "Operational consideration" section for details.

You can find application ID of the oldest uncommitted transaction, also from database snapshot output.

   db2 GET SNAPSHOT FOR DATABASE ON [database]
   ：
   Appl id holding the oldest transaction     = 504
   ：
   File number of first active log            = 0
   File number of last active log             = 2
   File number of current active log          = 2

Note:
When the application handle number was not included in message ADM1823E, "transaction log is full" situation may be caused by indoubt transaction remaining. Please refer another technote [DB2 LUW] Recovering indoubt transaction

1.2. Solution

Temporary workaround:

Terminate uncommitted transactions specified in step 1.1, using any of the methods listed:

• Issue commit request from application
• Issue rollback request from application
• Intentionally terminate the application by "force application" command
  For example, if you would like to terminate application with handle number 504, issue the following command:

  db2 "force application (504)"

  Note: Even if you got two-part application handle such as "0-504", specify just "504" instead of "0-504".
  Note: When a system application such as "db2reorg", "db2acd" etc, is the oldest uncommitted transaction owner, the "force application" command does not work for these system applications. It is possible these system applications are in lock wait and cannot release log resources. You can find the lock holder by running the "db2pd -wlock -alldbs" command. If you can find the lock holder causing the system application lock wait, terminate it by using the "force application" command.

Permanent solution

Modify your application program to issue commit more timely manner.

2. When too many changes (UPDATE/DELETE/INSERT) were performed and log area does not have enough space to store these log record.

Enlarge active log area, by increasing log file size and/or number of log files. Note: If there's any application which never commit or rollback, this method does not work.

2.1. find current parameter setting about log file.

db2 get db cfg for [database]

Refer to the following database configuration parameters

Log file size (4KB)                         (LOGFILSIZ)
Number of primary log files                (LOGPRIMARY)
Number of secondary log files               (LOGSECOND)
   :
Path to log files

Explanation of these parametes:

• Number of primary log files: LOGPRIMARY
  number of primary log files to be preallocated. These log files are created when the database was activated, It is recommended to prepare enough size to store necessary log record in normal workload, due to performance reason.
• Number of secondary log files: LOGSECOND
  number of secondary log files that are created only as needed. When all the primary log file are already used and need more log file, secondary log file will be created and used as needed. If the specified number of secondary log files are created and still need more log file, transaction log file was treated as full condition.
• Log file size: LOGFILSIZ
   size of each primary and secondary log file. Value should be specified in 4KB page Unit (that is, the value '1000' means 1000 x 4K = 4000KB).
• Path to log files
   location where the log files are stored.
  maximum total size of log space can be calculated by the formula:

  (LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4KB

  Please make sure "Path to log file" have enough space for maximum total size of log space.

2.2. solution

• Increasing size of each log files (LOGFILSIZ):
  db2 update db cfg for [database] using LOGFILSIZ [new value]
• Increasing number of log files (LOGPRIMARY / LOGSECOND)
  • Increasing number of primary log files
    db2 update db cfg for [database] using LOGPRIMARY [new value]
  • Increasing number of secondary log files (works immediately)
    db2 update db cfg for [database] using LOGSECOND [new value] IMMEDIATE

3. Use Advanced Log Space Management (ALSM)

Available starting from Db2 Version 11.5 Mod Pack 4, use Advanced Log Space Management (ALSM) to reduce your likelihood of hitting transaction log full conditions. The feature can be enabled by setting the DB2_ADVANCED_LOG_SPACE_MGMT registry variable to ON.
 

Note:
If you modified LOGSECOND value, it works immediately.
If you changed the LOGPRIMARY or LOGFILSIZ value, the database needs to be re-activated to make the change effective.
Therefore if you would not like to interrupt your application, increase LOGSECOND. It works without stopping application. Sum of LOGPRIMARY and LOGSECOND value cannot exceed 256 (in V11.5, the maximum total number of log files become 8192 in archived logging mode).