If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > 20gb of active logs! What could be wrong?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #2 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,398
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,398
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2012
Posts: 12
Where should I start looking?
Reply With Quote
  #6 (permalink)  
Old
∞∞∞∞∞∞
 
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:
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #8 (permalink)  
Old
∞∞∞∞∞∞
 
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.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #10 (permalink)  
Old
∞∞∞∞∞∞
 
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
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #13 (permalink)  
Old
∞∞∞∞∞∞
 
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).
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
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
File Type: txt sever.txt (116.5 KB, 5 views)
Reply With Quote
  #15 (permalink)  
Old
∞∞∞∞∞∞
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On