RE: DB froze with "SQL0964C The transaction log for the database is full"

RE: DB froze with "SQL0964C The transaction log for the database is full"

 

  

If you had something that reads the logs, you could do some reports to see
what kind of SQL updates/inserts deletes were being run in the
time frame. Recovery Expert or some other like tool. You could have also
run an application snapshot to see what the start and end of the
UOW was.







From:
"Dan Cook \(Corp\) "
<db2udbdba-ezmlmshield-x24890048.[Email address protected]
To:
"LazyDBA Discussion" <[Email address protected]
Date:
04/24/2008 04:04 PM
Subject:
RE: DB froze with "SQL0964C The transaction log for the database is full"



Hello Ed,

Thanks for your reply. I have seen this article as well as a few others
but this is to prevent such a situation. I am also reluctant of doing
this because this environment has been working smoothly without a glitch
for over 6 months.

So, is there a way to find out which process and/or data caused it?

On a personal note, I don't believe we know each other. I come from
Montréal moved out for good in California since last September.


Dan AKA Daniel Cook

-----Original Message-----
From: Edwards Ed [mailto:db2udbdba-ezmlmshield-x16771536.[Email address
protected]
Sent: Thursday, April 24, 2008 12:21
To: LazyDBA Discussion
Subject: RE: DB froze with "SQL0964C The transaction log for the database
is full"

Well,well, well!
It's Dan Cook! Hehe. How have you been? The link below maybe of
some help to you.

http://www-1.ibm.com/support/docview.wss?uid=swg21154329


It's Fridayyyyyyy! Partyyyyyyyy! Hehe.





Please take a few minutes to provide feedback on the quality of service
you received from our staff. The Department of Education values your
feedback as a customer. Commissioner of Education Dr. Eric J. Smith is
committed to continuously assessing and improving the level and quality of
services provided to you.Simply use the link below. Thank you in advance
for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




-----Original Message-----
From: Dan Cook (Corp)
[mailto:db2udbdba-ezmlmshield-x27340705.[Email address protected]
Sent: Thursday, April 24, 2008 2:03 PM
To: LazyDBA Discussion
Subject: DB froze with "SQL0964C The transaction log for the database is
full"

Hi LazyDBAs,



This is my first post. Last Thursday night one of our databases froze
with a "SQL0964C The transaction log for the database is full.
SQLSTATE=57011" message in our application log.



Symptoms were:



- Users could not login to the application using the Web UI



- Logs are closed at every 1st and 31st minute by cron; between
21:31 and 04:01, logs did not close

[[Email Address Removed] /data/psditst1/db2inst1/NODE0000/SQL00001/SQLOGDIR:

-rw------- 1 db2inst1 db2iadm1 106496 Apr 17 20:31 S0009939.LOG

-rw------- 1 db2inst1 db2iadm1 1773568 Apr 17 21:01 S0009940.LOG

-rw------- 1 db2inst1 db2iadm1 2129920 Apr 17 21:31 S0009941.LOG
<--

-rw------- 1 db2inst1 db2iadm1 34869248 Apr 18 04:01 S0009942.LOG
<--

-rw------- 1 db2inst1 db2iadm1 3944448 Apr 18 04:31 S0009943.LOG

-rw------- 1 db2inst1 db2iadm1 3645440 Apr 18 05:01 S0009944.LOG



- The cron triggered 00:15 online backup did not occur:

-rwxr-xr-x 1 115 102 278128348 Apr 17 18:18
PSDITST1.0.db2inst1.NODE0000.CATN0000.20080417181003.001.gz

-rwxr-xr-x 1 115 102 277055586 Apr 18 06:15
PSDITST1.0.db2inst1.NODE0000.CATN0000.20080418061002.001.gz

-rwxr-xr-x 1 115 102 278433816 Apr 18 12:15
PSDITST1.0.db2inst1.NODE0000.CATN0000.20080418121002.001.gz



- 01:00 reorg & runstat ran Ok



- 03:00 reorg check ran Ok



- There was no db locks or deadlocks



- Contents of tables could be selected although it took longer
than normal to have the output.



- Other databases in the instance were not affected.



- This is a small 9 GB database with a maximum of 20 concurrent
users and 60-70 processes with db connection



- There was no filesystem full condition



- Issue was resolved after the 04:00 restart of the
application.



DB Log configuration:

$ db2 get db cfg for psditst1 | grep -i log

Log retain for recovery status = RECOVERY

User exit for logging status = NO

Catalog cache size (4KB) (CATALOGCACHE_SZ) = 2048

Log buffer size (4KB) (LOGBUFSZ) = 1024

Log file size (4KB) (LOGFILSIZ) = 10000

Number of primary log files (LOGPRIMARY) = 5

Number of secondary log files (LOGSECOND) = 10

Changed path to log files (NEWLOGPATH) =

Path to log files =
/data/psditst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/

Overflow log path (OVERFLOWLOGPATH) =

Mirror log path (MIRRORLOGPATH) =

First active log file = S0010318.LOG

Block log on disk full (BLK_LOG_DSK_FUL) = NO

Percent of max active 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) = 100

Log retain for recovery enabled (LOGRETAIN) = RECOVERY

User exit for logging enabled (USEREXIT) = OFF

HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC

First log archive method (LOGARCHMETH1) = LOGRETAIN

Options for logarchmeth1 (LOGARCHOPT1) =

Second log archive method (LOGARCHMETH2) = 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

Log pages during index build (LOGINDEXBUILD) = OFF



Version is

DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL08025" with level identifier "03060106".

Informational tokens are "DB2 v8.1.1.112", "s060429", "U807381", and
FixPak "12".



On AIX 5.3.0



Is there a way to find what caused this?





Dan

3 month young de facto not yet DBA











---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




DB2 & UDB email list listserv db2-l LazyDBA home page