[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 38592
  • Last Modified:

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

it says how can i clear and incrrease it it plz give steps
thanks
bobby

SQL0964C  The transaction log for the database is full.  SQLSTATE=57011
0
bobby2929
Asked:
bobby2929
  • 2
1 Solution
 
db2inst1Commented:

You might have to tune/ increase these db params

LOGFILSIZ
LOGSECOND

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.
0
 
bobby2929Author Commented:
thts kool increasing logfilsiz and logsecond cant we elimnate all files nd start from begining
thanks
bobby
0
 
db2inst1Commented:

You can safely do that using

db2 prune log file command

take a look at this link

http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0001992.htm

0
 
ghp7000Commented:
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.

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now