Database encounters log-full error even though it has the NUM_LOG_SPAN parameter set

Answer

In certain situations the database may run out of logging space before it reaches the NUM_LOG_SPAN threshold.

LOGPRIMARY is a database parameter which allows you to specify the number of primary log files to be preallocated. LOGSECOND specifies the number of secondary log files which are used to accommodate overflow from the primary log files when needed. Together these log files establish a fixed amount of storage allocated to the recovery log files. This storage is also known as the active log as it is used by active transactions.

NUM_LOG_SPAN is another parameter used to specify a limit to how many log files one transaction can span. The purpose of NUM_LOG_SPAN is to prevent transactions from holding up the active log and causing database to run out of logging space. If active log is held up, the database is unable to reuse older logs until the transaction either commits or rolls back. In the meantime, other transaction may be filling up the active log causing it to run out of space. When NUM_LOG_SPAN is set, the database manager will roll back a transaction when it detects that it reached the log span threshold.

It is possible for a transaction to hit the log-full condition before it reaches the NUM_LOG_SPAN threshold. One way for this to happen is when the log reserved space causes database to run out of log space before hitting the log span limit. Log reserved space is the logging space reserved for rollback. Namely, database logger reserves space for compensation log records for certain transactions so it has enough log space to perform rollback if needed. Take, for example, a transaction which performs inserts. For each insert, logger reserves space for the corresponding undo log record in case it is needed. This guarantees that transaction will not run out of log space while being rolled back. Consider a case where NUM_LOG_SPAN is 8 and active log size is 10. It is possible that reserved space may push to the end of log space even as transaction is writing to log file 7. In this case, the log span limit would never be reached and application would receive log-full error as expected.

Another case when database may receive log-full condition first is due to timing when the NUM_LOG_SPAN value is set too close to the active log size (LOGPRIMARY + LOGSECOND). The database logger checks periodically if any transaction violates NUM_LOG_SPAN. The database may reach the log span limit and run out of log space between two NUM_LOG_SPAN checks. In this case, the logger detects the log-full condition before it detects log span violation, and reports the former first. For example, lets assume NUM_LOG_SPAN is 9 and active log size is 10. Logger may have checked NUM_LOG_SPAN when transaction was writing to log file 8 (still below the limit). Then it gets to log file 9 and reaches the end of log space (due to reserved space) before logger performs the next NUM_LOG_SPAN check. Although the application technically breached the log span limit in this case, the log full condition was detected first. As a result, the application receives the log-full error.

To mitigate this issue you should first ensure that NUM_LOG_SPAN is set to somewhere between 60-80% of active log size (LOGPRIMARY + LOGSECOND). If the problem persists, try increasing the log file size (LOGFILSIZ). There is no optimal combination of values for these parameters because they depend on the user data, and other database and application characteristics. Appropriate ratio will depend on application needs, and may have to be found using the trial-and-error approach.