DB2 transaction logs are retained in the active log path (filling up the disk space)

In last few months we have seen many issues where users have reported that the log archiving is not happening correctly. DB2 transaction logs are retained in the active log path causing primary log path to fill up. This can have serious business impact on the production system. Please refer this article to be aware of such symptoms, understanding the cause, what diagnostics can be collected to confirm the behaviour and how to workaround/fix. 

 
Following diagnostics can be collected to troubleshoot this behaviour: 

 (a)  db2support output_directory -s -d database_name -c 
 (b)  "ls -ltr" output of the active and archive log paths 
 (c)  "lsof" output of the active and archive log paths 
 (d)   db2pd -db database_name -dpsdbcb 3  
 
Sometimes log file getting left in the active log path could be expected or it could be a defect in DB2. 
 
Upon investigation we found such behaviour can be a bug in DB2 (specific to DB2 version 10.5 Fix Pack 5 ). 
If your db2 instance is on different db2level then its quite possible db2 is working as designed, so above diagnostic data can help to get a clear picture of the situation. 

 
Following are the current known issues : 

 
IT08728: DB2 MAY LEAK FILE HANDLES WHEN USING HADR, OR READLOG/QREP/CDC (OR ANY LOG READER). 
http://www-01.ibm.com/support/docview.wss?uid=swg1IT08728 

In specific timing condition, when db2ReadLog API is called to read last page of current log, given log will never be closed. This might result the file handle leak in the db2sysc process. Depending on O/S, you need to use an operating system tool to display file handles (ex: 'lsof' on AIX).  Look for excessive number of file handles open for database log files (Snnnnnnn.LOG). This problem is specific to DB2 version 10.5 Fix Pack 5.Problem first fixed in DB2 version 10.5 Fix Pack 6. 
  

IT07968: IN SPECIFIC TIMING CONDITIONS, DB2READLOG API CALLS (USED E.G BY CDC) MIGHT RESULT IN LOGS BEING LEFT IN ACTIVE LOG PATH. 
http://www-01.ibm.com/support/docview.wss?uid=swg1IT07968 

In specific timing condition, when db2ReadLog API is called to read last page of current log, given log will never be re-used and will stay in active log path until database is deactivated.This might result in multiple logs being left in active log path filling up the disk space. db2ReadLog is mainly used by replication solutions like IBM Q Replication or IBM InfoSphere Change Data Capture. Problem is specific to DB2 version 10.5 Fix Pack 5. Problem first fixed in DB2 version 10.5 Fix Pack 6. 
  

IT10707: WITH MULTIPLE DB2READLOG API CALLERS (HADR, CDC, ETC), LOG FILES MAY NOT BE RENAMED RESULTING IN PRIMARY LOG PATH TO FILL UP 
http://www-01.ibm.com/support/docview.wss?uid=swg1IT10707 

Similar to IT07968, and found predominantly when HADR and another db2readlog caller are used together,  in specific timing condition, when db2ReadLog API is called to read last page of current log, given log will never be re-used and will stay in active log path until database is deactivated. This might result in multiple logs being left in active log path 
filling up the disk space. db2ReadLog is mainly used by replication solutions like IBM Q Replication or IBM InfoSphere,Change Data Capture. Problem first fixed in DB2 version 10.5 Fix Pack 7. 
  

So as a proper fix, we highly recommend to upgrade DB2 instance to the latest fixpack in DB2 V10.5 release (or at least to DB2 version 10.5 Fix Pack 7) 
  

By simply upgrading db2 instance you won't be able to clear the existing mess as upgrading instance will avoid next occurrences but in order to clear the current mess we recommend following approach: 
  (a) db2 archive log for database <database_name> /* if (a) doesn't help then move to (b) */ 
  (b) db2 prune logfile prior to <First active log file> 
  (c) If (a) and (b) doesn't help then attempt to deactivate db : db2 deactivate db <database_name> 

 
Thanks,

Shashank Kharche

IBM DB2 LUW Lab