Encountering "...transaction log...full" error 1

[SantaMufasa]

I am receiving the error message, "[IBM][CLI Driver][DB2/SUN] SQL0964C The transaction log for the database is full. SQLSTATE=57011".

What are typical troubleshooting and remedial steps for this issue? (Actual commands to try would be helpful since I am an Oracle DBA wearing the "poorly-fitting-for-me" DB2 DBA hat.)

DB2 Version: 8.2. I have full Administrative/DBA privileges on the system. We have 4.3GB of space available on the file system that holds the DB2 transaction log.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Truusvlugindewind]

This might help:

http://www-128.ibm.com/developerworks/db2/library/techarticle/0301kline/0301kline.html

 

[gregsimpson]

Mufasa,

to check what you're logging parameters are currently set to

1) connect to the database

2) Issue db2 get db cfg

The line you're interested in are as follows

Log file size (4KB) (LOGFILSIZ) = 1000
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 2
Changed path to log files (NEWLOGPATH) =
Path to log files = /db2data/db2issb1/gregdbidb/data/db2issb1/NODE0000/SQL00001/SQLOGDIR/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file =
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent of max active log space by transaction(MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF


Taking the logfilsiz as an example. If you wish to change this paramter the syntax would be

db2 update database config for mufasadb using logfilsiz 2000

The other parameters all follow a similar pattern.

Hope this helps. First step would be to check whether you have circular or archival logging, I would say. The default is circular and you may well find archival more appropriate, depending on your system. Please post again, if you need some further help.

Thanks
Greg

 

[SantaMufasa]

Thanks, Greg, your post was particularly helpful...Have a Purple Star.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]