ADM1823E active log file full

20 March,2010 by Jack Vamvas

I found this message in the db2diag.log :

ADM1823E  The active log is full and is held by application handle
  "371".  Terminate this application by COMMIT, ROLLBACK or FORCE
  APPLICATION.

Doing some research there were a couple of potential causes

1.Potentially long running query - not commiting i.e database configured to run 12 log files on a circular.
If the transaction has not been commited and you go through the 12 log files - then try to write to log file 1 again - which is still holding the open transaction, then you've got a problem. This is one possible reason , and requires further investigation
2.Known problem of DB2 Health Monitor coming on every 2 hrs - despite having it turned off - requires Environment variable changes,more details below. This has already been dealth with via the DB2_FMP_COMM_HEAPSZ=0

The actual resolution of the problem : The problem reappeared after 2 hrs : Did the following:

db2 get snapshot for applications on MONITOR

and look for application handle "371".

This showed that the last handles was in Lock-wait state , and the application was DB2HMON
I then released the lock db2 " force application ( 371 ) "

On this basis I discovered a current major issue with DB2 - regarading DB2 Health Monitor and OLTP dbs . Even if you have the Health Monitor turned OFF ,
every 2 hrs the Health Monitor will start and check everything is OK - run some maintenance etc - at this point there is a lot locking etc

I issued the following commands - see Appendix 1 on every instance on the server - and did a full restart. Note : the following changes are environment variables

db2 update dbm cfg using HEALTH_MON OFF
db2set DB2_FMP_COMM_HEAPSZ=0

 Ref:Jack Vamvas(http://www.dba-db2.com)

 

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on ADM1823E active log file full

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright