How to get immediate relief when log space get full?

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.

***

Solutions:

(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:

http://www-01.ibm.com/support/docview.wss?uid=swg21664899

 

Cheers!
Ravi

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: