Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    32

    Unanswered: not enough space to allocate active log files error while login

    I changed the logfilesiz to 10000, logprimary=20 and logsecond=40

    after that now when i want to login using db2 connect it says SQL1762N Unable to connect to database because there is not enough space to
    allocate active log files. SQLSTATE=08004, i cant even update the logfilesiz,logprimary and secondary ? help


    Code:
    C:\Program Files\IBM\SQLLIB\BIN>db2 update database configuration for IMRM2 usin
    g logfilsiz 10000
    DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
    SQL1363W  Database must be deactivated and reactivated before the changes to
    one or more of the configuration parameters will be effective.
    
    C:\Program Files\IBM\SQLLIB\BIN>db2 UPDATE DATABASE CONFIGURATION FOR IMRM2 USIN
    G LOGPRIMARY 20
    DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
    SQL1363W  Database must be deactivated and reactivated before the changes to
    one or more of the configuration parameters will be effective.
    
    
    C:\Program Files\IBM\SQLLIB\BIN>db2 update db cfg for IMRM2 using logsecond 40
    DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
    
    C:\Program Files\IBM\SQLLIB\BIN>db2 get db cfg
    
     
     Log file size (4KB)                         (LOGFILSIZ) = 10000
     Number of primary log files                (LOGPRIMARY) = 20
     Number of secondary log files               (LOGSECOND) = 40
     Changed path to log files                  (NEWLOGPATH) =
     Path to log files                                       = /home/db2inst2/db2ins
    t2/NODE0000/SQL00003/LOGSTREAM0000/
     Overflow log path                     (OVERFLOWLOGPATH) =
     Mirror log path                         (MIRRORLOGPATH) =
     First active log file                                   =
     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) = 520
    
     HADR database role                                      = STANDARD
     HADR local host name                  (HADR_LOCAL_HOST) =
     HADR local service name                (HADR_LOCAL_SVC) =
     HADR remote host name                (HADR_REMOTE_HOST) =
     HADR remote service name              (HADR_REMOTE_SVC) =
     HADR instance name of remote server  (HADR_REMOTE_INST) =
     HADR timeout value                       (HADR_TIMEOUT) = 120
     HADR target list                     (HADR_TARGET_LIST) =
     HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
     HADR spool log data limit (4KB)      (HADR_SPOOL_LIMIT) = 0
     HADR log replay delay (seconds)     (HADR_REPLAY_DELAY) = 0
     HADR peer window duration (seconds)  (HADR_PEER_WINDOW) = 0
    
     First log archive method                 (LOGARCHMETH1) = OFF
     Archive compression for logarchmeth1    (LOGARCHCOMPR1) = OFF
     Options for logarchmeth1                  (LOGARCHOPT1) =
     Second log archive method                (LOGARCHMETH2) = OFF
     Archive compression for logarchmeth2    (LOGARCHCOMPR2) = OFF
     Options for logarchmeth2                  (LOGARCHOPT2) =
     Failover log archive path                (FAILARCHPATH) =
     Number of log archive retries on error   (NUMARCHRETRY) = 5
     Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
     Vendor options                              (VENDOROPT) =
    
     Auto restart enabled                      (AUTORESTART) = ON
     Index re-creation time and redo index build  (INDEXREC) = SYSTEM (RESTART)
     Log pages during index build            (LOGINDEXBUILD) = OFF
     Default number of loadrec sessions    (DFT_LOADREC_SES) = 1
     Number of database backups to retain   (NUM_DB_BACKUPS) = 12
     Recovery history retention (days)     (REC_HIS_RETENTN) = 366
     Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = OFF
    
     TSM management class                    (TSM_MGMTCLASS) =
     TSM node name                            (TSM_NODENAME) =
     TSM owner                                   (TSM_OWNER) =
     TSM password                             (TSM_PASSWORD) = *****
    
     Automatic maintenance                      (AUTO_MAINT) = ON
       Automatic database backup            (AUTO_DB_BACKUP) = OFF
       Automatic table maintenance          (AUTO_TBL_MAINT) = ON
         Automatic runstats                  (AUTO_RUNSTATS) = ON
           Real-time statistics            (AUTO_STMT_STATS) = ON
           Statistical views              (AUTO_STATS_VIEWS) = OFF
           Automatic sampling                (AUTO_SAMPLING) = OFF
         Automatic statistics profiling    (AUTO_STATS_PROF) = OFF
           Statistics profile updates        (AUTO_PROF_UPD) = OFF
         Automatic reorganization               (AUTO_REORG) = OFF
    
     Auto-Revalidation                          (AUTO_REVAL) = DEFERRED
     Currently Committed                        (CUR_COMMIT) = ON
     CHAR output with DECIMAL input        (DEC_TO_CHAR_FMT) = NEW
     Enable XML Character operations        (ENABLE_XMLCHAR) = YES
     WLM Collection Interval (minutes)     (WLM_COLLECT_INT) = 0
     Monitor Collect Settings
     Request metrics                       (MON_REQ_METRICS) = BASE
     Activity metrics                      (MON_ACT_METRICS) = BASE
     Object metrics                        (MON_OBJ_METRICS) = BASE
     Unit of work events                      (MON_UOW_DATA) = NONE
       UOW events with package list        (MON_UOW_PKGLIST) = OFF
       UOW events with executable list    (MON_UOW_EXECLIST) = OFF
     Lock timeout events                   (MON_LOCKTIMEOUT) = NONE
     Deadlock events                          (MON_DEADLOCK) = WITHOUT_HIST
     Lock wait events                         (MON_LOCKWAIT) = NONE
     Lock wait event threshold               (MON_LW_THRESH) = 5000000
     Number of package list entries         (MON_PKGLIST_SZ) = 32
     Lock event notification level         (MON_LCK_MSG_LVL) = 1
    
     SMTP Server                               (SMTP_SERVER) =
     SQL conditional compilation flags         (SQL_CCFLAGS) =
     Section actuals setting               (SECTION_ACTUALS) = NONE
     Connect procedure                        (CONNECT_PROC) =
     Adjust temporal SYSTEM_TIME period (SYSTIME_PERIOD_ADJ) = NO
     Log DDL Statements                      (LOG_DDL_STMTS) = NO
     Log Application Information             (LOG_APPL_INFO) = NO
     Default data capture on new Schemas   (DFT_SCHEMAS_DCC) = NO
     Database is in write suspend state                      = NO
    
    
    C:\Program Files\IBM\SQLLIB\BIN>db2 connect to IMRM2 user db2inst1
    Enter current password for db2inst1:
    SQL1762N  Unable to connect to database because there is not enough space to
    allocate active log files.  SQLSTATE=08004
    
    C:\Program Files\IBM\SQLLIB\BIN>db2 connect to IMRM2 user db2inst1
    Enter current password for db2inst1:
    SQL1762N  Unable to connect to database because there is not enough space to
    allocate active log files.  SQLSTATE=08004
    
    C:\Program Files\IBM\SQLLIB\BIN>db2 update db cfg for IMRM2 using logsecond 20
    SQL30082N  Security processing failed with reason "3" ("PASSWORD MISSING").
    SQLSTATE=08001
    
    C:\Program Files\IBM\SQLLIB\BIN>db2 update db cfg for IMRM2 using logsecond 20 u
    ser db2inst1
    SQL0104N  An unexpected token "user" was found following "<identifier>".
    Expected tokens may include:  "ALT_COLLATE".  SQLSTATE=42601
    
    C:\Program Files\IBM\SQLLIB\BIN>db2 connect to IMRM2 user db2inst1
    Enter current password for db2inst1:
    SQL30082N  Security processing failed with reason "24" ("USERNAME AND/OR
    PASSWORD INVALID").  SQLSTATE=08001
    
    C:\Program Files\IBM\SQLLIB\BIN>db2 connect to IMRM2 user db2inst1
    Enter current password for db2inst1:
    SQL1762N  Unable to connect to database because there is not enough space to
    allocate active log files.  SQLSTATE=08004
    
    C:\Program Files\IBM\SQLLIB\BIN>db2 connect to IMRM2 user db2inst2
    Enter current password for db2inst2:
    SQL30082N  Security processing failed with reason "24" ("USERNAME AND/OR
    PASSWORD INVALID").  SQLSTATE=08001
    
    C:\Program Files\IBM\SQLLIB\BIN>db2 connect to IMRM2 user db2inst2
    Enter current password for db2inst2:
    SQL1762N  Unable to connect to database because there is not enough space to
    allocate active log files.  SQLSTATE=08004
    
    C:\Program Files\IBM\SQLLIB\BIN>

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can move the log path to a new location with sufficient space (best option) or make the files smaller/fewer (not the best solution).

    You can update the db configuration without logging in to the database:

    db2 update db cfg for IMRM2 using NEWLOGPATH XXXXXXXXXXXXXXXX

    You may have to take an offline backup before you access the database.
    Last edited by Marcus_A; 07-25-12 at 17:05.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Firstly, changing LOGSECOND won't help you, as only the primary log files are allocated during the database activation. Secondly, try specifying DEFERRED option:

    db2 update db cfg for IMRM2 using logprimary 5 deferred
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    marshell08, another thing to consider. Do you really need a Log File size of 10000? That is the number of 4K pages.

    It works out to a size 10,000 * 4096 = 40,960,000 or 40 GB (each). With 20 Primary logs the total space is 819,200,000 or almost 900 MB.

  5. #5
    Join Date
    Jun 2012
    Posts
    32
    Stealth_DBA yes its a production database anyways i solved this issue by the changing the path to a drive that has more space.

    Problem Solved thank you guys

Posting Permissions

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