SQL0964C  The transaction log for the database is full.  SQLSTATE=57011

Posted on 2005-05-11
Last Modified: 2011-08-18
it says how can i clear and incrrease it it plz give steps

SQL0964C  The transaction log for the database is full.  SQLSTATE=57011
Question by:bobby2929
    LVL 7

    Expert Comment


    You might have to tune/ increase these db params


    db2 update db cfg for <dbname> USING <paramname> <newvalue>
    Also try

    db2 ? SQL0964C

    You might be able to find an alternative depending on when you receive this error.

    Author Comment

    thts kool increasing logfilsiz and logsecond cant we elimnate all files nd start from begining
    LVL 7

    Expert Comment


    You can safely do that using

    db2 prune log file command

    take a look at this link

    LVL 13

    Accepted Solution

    forget about db2prune,this is too advanced for you right now I think and is not what you think it is.
    What you have to know is that the default setup for DB2 is to used the log files in a circular manner, that means if you have 20 log files defined, db2 cycles through the 20 logs and then re uses log number 1 when log 20 becomes full.
    You will get error message:
    SQL0964C  The transaction log for the database is full.  SQLSTATE=57011
    when db2 has cycled trhough all the available logs and then goes to use log number 1, only to find that log number 1 cannot be reused because log number 1 still contains uncommitted transactions. So, db2 will rollback all the transactions that were part of the unit of work that caused the SQL0964 to be issued, now all those logs that contained those transactions are available again. If you re try the same statement under the same conditions, you will again get the same message, so there a number of ways to fix this problem.

    To fix this problem, you have to know what the current settings are:
    db2 get db cfg for <database name>

    The easiest way is to simply increase the number of log files available:
    db2 update db cfg for <database name> using LOGPRIMARY  <an integer number at least two times higher than the cuurent setting>
    Or, simply increase the size of each log file:
    db2 update db cfg for <database name> using LOGFILSIZ  5000
    This means each log file willbe 20 MB in size
    Or, make secondary logs available to DB2 if DB2 should run out of primary logs. These secondary logs will be erased by DB2 when they are no longer needed.
    db2 update db cfg for <database name> using LOGSECOND < an integer number at least two times higher than the cuurent setting for LOGPRIMARY>
    If you are using DB2 v8.1,  you don't need to do anything else. On any other version, all applications must disconnect from the database, the new settings take effect when the first application re connects to the database.

    If you have concurrent applications running, make sure these applications have committed their transactions before trying the new setup.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Suggested Solutions

    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now