Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: Help managing DB2 log files

    DB2 V9.5 on Windows

    I have a Development box with 6 Development database in one Instance of DB2.

    Until they get our SAN installed, I'm having to constantly deal with space problems on the box.

    I ran across a bunch of directories with what I think are log files (some of them quite old - like from Jan 2008) and I can't figure out what DB they belong to and if they can be deleted - or at least cleaned up.

    The folder structures on the Windows Servers look like this:

    E:\DB2\NODE0000\SQL00012\SQLOGDIR
    E:\DB2\NODE0000\SQL00013\SQLOGDIR

    the SQLOGDIR folders contain files like S0000000.LOG, S0000001.LOG, S0000002.LOG, etc.. and a couple of the SQLOGDIR folders are over 2 GB in size!

    Can I reclaim any of this space and if so, how to manage this going forward...

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    how many log files defined in db cfg ?
    are you archiving the logs - this should clean up logs
    do db2 get db cfg for dbname and check logprimary logsecond logarchmeth1...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2006
    Posts
    97
    first thing you need to find out How many log files you are using in your database.
    Number of primary log files (LOGPRIMARY) = 30
    Number of secondary log files (LOGSECOND) = 20

    and Path to log files What is this location???? and no should match no logs ( active once at least)

    also verify

    Overflow log path (OVERFLOWLOGPATH) =
    Mirror log path (MIRRORLOGPATH) =

    finally

    First log archive method (LOGARCHMETH1) = ????

    If none of them have those directories . Generally safe remove it.

  4. #4
    Join Date
    Dec 2007
    Posts
    288
    Thank you. Gives me something to start looking at... The databases on this box are a mix of archive logging and circular logging.. The DBs that we run batch cycles against to load data and do reporting are archive logging and the primary and archive log paths are clearing defined. I find it annoying to have to go out and clean up old archive logs after and offline backup.. any way to automate that?

  5. #5
    Join Date
    Dec 2008
    Posts
    76
    You will have to know the database the logs belong to to rationally decide what to do with them. That depends on your recovery strategy.

    To eliminate logs the command is: db2 prune logfile prior to <LOG FILE NAME>

    This will eliminate all logs older than the named file.

  6. #6
    Join Date
    Dec 2007
    Posts
    288
    Thanks for all your help. I looked through the CONFIG parameters for each DB and identified which logs were for which DB. I didn't build the environment.. just trying to do some cleanup.

    Back to the archive logs.. Is there any 'automated' kind of way to tell DB2 to clean up old archive logs after itself when it performs an offline backup?

    Seems like I spend WAY too much time cleaning up archive logs..

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by itsonlyme44
    Back to the archive logs.. Is there any 'automated' kind of way to tell DB2 to clean up old archive logs after itself when it performs an offline backup?

    Seems like I spend WAY too much time cleaning up archive logs..
    If you do a weekly offline backup, then delete any archive log files that are older than 8 days old.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Dec 2007
    Posts
    288
    it's really all over the place. If batch cycles are running against any given DB - then we might do an offline backup every day. some only once a week

    it's just a pain figuring out when the last time a full offline backup ran for any particular DB and then deleting logs older than that.. seems there should be a way to automate it

  9. #9
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Quote Originally Posted by itsonlyme44
    it's just a pain figuring out when the last time a full offline backup ran for any particular DB and then deleting logs older than that.. seems there should be a way to automate it

    You can very well right batch scripts for backup and online deletion and then schedule them at the OS level.... or else you can utilize automatic maintenance features and / or Task Center features.
    IBM Certified Database Associate, DB2 9 for LUW

  10. #10
    Join Date
    Dec 2007
    Posts
    288
    Thanks. I guess I worded my question badly.. wondering how other people do cleanup? for most of my databases (PROD and Development) the backups cannot be scheduled at the OS level because the backups are kicked off by a 3rd party scheduler during a batch cycle.

    So I guess what you're saying is as long as I can confirm that the Full-OFFLINE backup has run against a particular Database, I can schedule a job to delete all of the old archive files?? I've been reading until my eyes hurt and I can't seem to find where this can be automated within the tools..

Posting Permissions

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