Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2012
    Posts
    12

    20gb of active logs! What could be wrong?

    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?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,410
    Quote Originally Posted by colicoid View Post

    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".
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2012
    Posts
    12
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,410
    Well, I guess you'll have to figure out why DB2 fails to archive logs to d:\dbmain\dprod2\succ\
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2012
    Posts
    12
    Where should I start looking?

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,361
    Check your db2diag.log. Does D: have free space? Active, archived and failover log dir reside on D:

  7. #7
    Join Date
    Jan 2012
    Posts
    12
    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?

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,361
    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.

  9. #9
    Join Date
    Jan 2012
    Posts
    12
    Do you think db2 is thinking: there isn't 20gb free space so I can't move these inactive archive logs into that folder?

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,361
    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

  11. #11
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    use "db2 prune" instead of deleting them by hand. it is more safe
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  12. #12
    Join Date
    Jan 2012
    Posts
    12
    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?

  13. #13
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,361
    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).

  14. #14
    Join Date
    Jan 2012
    Posts
    12
    Here are all the severe errors from db2diag from the last 3D.
    A lot of heap memory stuff...
    Attached Files Attached Files

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,361
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •