I have a db with archive logging. Default number of primary/secondary logs.
The log directory has 626 log files taking up 20Gb of space!
The directory for successful archived logs (managed by DB2 - not user_exit) is just 3gb.

It seems to me that logs are not getting archived for some reason? Could 626 log files really be active? How can I determine if they are active?

Any ideas?

20 GB of log space isn't something particularly unusual. And it's very unlikely that you have 626 active log files, unless you have infinite logging enabled, but you'd know if you did, wouldn't you.

I'd start with showing us the relevant database configuration parameters ("db2 get db cfg | grep -i log" would do) plus the output of "db2pd -d yourdb -logs".

Here is the information.

Log retain for recovery status = NO
User exit for logging status = YES

Log buffer size (4KB) (LOGBUFSZ) = 99

Log file size (4KB) (LOGFILSIZ) = 10000
Number of primary log files (LOGPRIMARY) = 8
Number of secondary log files (LOGSECOND) = 12
Changed path to log files (NEWLOGPATH) =
Path to log files = D:\dbmain\dtest\
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file = S0003796.LOG
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Block non logged operations (BLOCKNONLOGGED) = NO
Percent max primary log space by transaction (MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

Percent log file reclaimed before soft chckpt (SOFTMAX) = 720
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF

First log archive method (LOGARCHMETH1) = DISK:\dbmain\dprod2\succ\
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) = D:\dbmain\dprod2\fail\
Number of log archive retries on error (NUMARCHRETRY) = 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
Vendor options (VENDOROPT) =

Log pages during index build (LOGINDEXBUILD) = OFF

----------------------------------------------------------------

Database Partition 0 -- Database DTEST -- Active -- Up 0 days 09:50:14 -- Date 11/30/2012 13:49:31

Logs:
Current Log Number 3796
Pages Written 7021
Method 1 Archive Status Failure
Method 1 Next Log to Archive 3796
Method 1 First Failure 3179
Method 2 Archive Status n/a
Method 2 Next Log to Archive n/a
Method 2 First Failure n/a
Log Chain ID 2
Current LSN 0x0051341759EE

Address StartLSN State Size Pages Filename
0xFC1ACF74 0x005132608000 0x00000000 10000 10000 S0003796.LOG
0xFC1AFED4 0x005134D18000 0x00000000 10000 10000 S0003797.LOG
0xFC1AFF74 0x005137428000 0x00000000 10000 10000 S0003798.LOG
0xFC1BB1D4 0x005139B38000 0x00000000 10000 10000 S0003799.LOG
0xFC1BB274 0x00513C248000 0x00000000 10000 10000 S0003800.LOG
0xFC1BB314 0x00513E958000 0x00000000 10000 10000 S0003801.LOG
0xA0464E54 0x005141068000 0x00000000 10000 10000 S0003802.LOG
0xA04672E4 0x005143778000 0x00000000 10000 10000 S0003803.LOG

Well, I guess you'll have to figure out why DB2 fails to archive logs to d:\dbmain\dprod2\succ\

Where should I start looking?

Check your db2diag.log. Does D: have free space? Active, archived and failover log dir reside on D:

There's not a lot of space there. Like 7gb. That's why I need to clean out log files.

If I make a full backup, can I then safely delete all the prior archive logs?
The logs are just needed from backup time and onwards right?

Yes, you can delete old archived logs if you don't need them. You need them if you have to restore from a backup image and rollforward to some point in time.

Do you think db2 is thinking: there isn't 20gb free space so I can't move these inactive archive logs into that folder?

If there is no space in the archived log dir and failover log dir, the logs will accumulate in the active log dir. You need some script or something to cleanup old archived log. I use auto-pruning using the following db cfg parameters:

Number of database backups to retain (NUM_DB_BACKUPS) = 14
Recovery history retention (days) (REC_HIS_RETENTN) = 14
Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = ON

use "db2 prune" instead of deleting them by hand. it is more safe

Thanks everyone. You've been very helpful!

Just to make sure I got this, to clear out transaction logs I would:
1. Make a backup (online/offline doesn't matter)
2. prune logfile prior to S0003796.LOG (where S0003796.LOG = first active logfile)
Right?

Yes, you can delete the logs prior to S0003796.LOG, but only if you don't need them for recovery. To be on a safe side, delete logs that are more than n weeks old.

Once log archival has been resumed (you never posted the error logged in the db2diag.log, is it due to out of space or some other reason?), you need to find some way to manage them - one way is to use:
IBM DB2 9.7 Information Center for Linux, UNIX, and Windows

I setup auto-pruning and so far it's been working ok.


Another way is to use PRUNE HISTORY with DELETE (I use this one on the servers where auto-pruning is not available).

Here are all the severe errors from db2diag from the last 3D.
A lot of heap memory stuff...

I don't see any errors related to log archival.

You may want to look at "Database will come up with hidden buffer pools" error for db DPROD. These hidden bufferpools are very small -> the db will perform poorly. You need to make sure that the config of all active databases doesn't exceed instance_memory.