help required dropping 200 gig db getting long tra
Karl Oliver couldn't drop a ~200GB database on IDS 11.50.FC6 (HP-UX): the DROP DATABASE kept hitting the long-transaction high-water mark even with database logging switched off, filling his 99 logical logs (LTXHWM 40 / LTXEHWM 50). Jack Parker suggested raising the marks to around 60/70 and, more practically, dropping tables in small batches first; another poster suggested disabling smart blob logging (a 120GB smart-blob table was suspected). David explained that space-allocation/extent maintenance is still logged regardless of database logging. Karl gave up and reinitialised the test instance (hitting a transient assertion failure during log backup while adding spaces), which got him working, so the original drop issue itself was never resolved.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IBM Informix Dynamic Server Version 11.50.FC6 HP-UX wuasp132 B.11.23 U 9000/800 3 I have turned logging off yet still get long transaction dropping database. What do I do now ?
more info
LTXHWM 40 # Long transaction high water mark percentage
LTXEHWM 50 # Long transaction high water mark (exclusive)
36 of 99 logs were used
Have you tried turning off logging (use ontape)
or droppin ga few tables at a tie?
j.
On Aug 1, 2012, at 7:43 PM, KARL OLIVER wrote:
> more info=20
> LTXHWM 40 # Long transaction high water mark percentage=20
> LTXEHWM 50 # Long transaction high water mark (exclusive)=20
>=20> 36 of 99 logs were used=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
yes have logging is off.
i am thinking changing these params might help plus adding more logs
LTXHWM 40 # Long transaction high water mark percentage
LTXEHWM 50 # Long transaction high water mark (exclusive)
I have 99 logs using approx 1 gig.
Biggest table in smart blob is about 120 gig which is probbaly the problem
40 and 50 is pretty low, 60 and 70 are generally enough, but if you are =
filling that much log space, then you need to break down the work into =
smaller chunks to not do so. Hence, try dropping a few tables at a time =
until you can take the entire db out.
j.
On Aug 1, 2012, at 7:53 PM, KARL OLIVER wrote:
> yes have logging is off.=20
> i am thinking changing these params might help plus adding more logs=20=
> LTXHWM 40 # Long transaction high water mark percentage=20
> LTXEHWM 50 # Long transaction high water mark (exclusive)=20
>=20> I have 99 logs using approx 1 gig.=20
> Biggest table in smart blob is about 120 gig which is probbaly the =
problem=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
thanks for ideas . I decide to initialise instamce as it is a test one. Much quicker, already wasted 2 hours Black mark against IBM for logging drop db when logging is OFF. It does not make sense
Even having problems initializing! 2nd black mark against IBM 3:22:26 Logical Log 6 - Backup Started 13:22:26 stack trace for pid 28492 written to /backup/af.43fd652 13:22:26 Assert Failed: No Exception Handler 13:22:26 IBM Informix Dynamic Server Version 11.50.FC6 13:22:26 Who: Session(20, paqis@auast120, 128, c00000007a1ce4c0) Thread(87, logbackup, c00000007a18dd08, 1) File: mtex.c Line: 491 13:22:26 Results: Exception Caught. Type: MT_EX_OS, Context: fpe 13:22:26 Action: Please notify IBM Informix Technical Support. 13:22:26 See Also: /backup/af.43fd652
Are you initializing with -ivy? Or just -v or not anything? j. On Aug 1, 2012, at 9:30 PM, KARL OLIVER wrote: > Even having problems initializing!=20 > 2nd black mark against IBM=20 >=20 > 3:22:26 Logical Log 6 - Backup Started=20 > 13:22:26 stack trace for pid 28492 written to /backup/af.43fd652=20 > 13:22:26 Assert Failed: No Exception Handler=20 > 13:22:26 IBM Informix Dynamic Server Version 11.50.FC6=20 > 13:22:26 Who: Session(20, paqis@auast120, 128, c00000007a1ce4c0)=20 >=20 > Thread(87, logbackup, c00000007a18dd08, 1)=20 >=20 > File: mtex.c Line: 491=20 > 13:22:26 Results: Exception Caught. Type: MT_EX_OS, Context: fpe=20 > 13:22:26 Action: Please notify IBM Informix Technical Support.=20 > 13:22:26 See Also: /backup/af.43fd652=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20
all going now was doing init -i was past that stage died when I was adding spaces and backing up logs. Normally I would open PMR but wont bother since I have it working now
Have you tried turning off smart blob logging?
No, logging for the database may be off but the space allocation lists for the instance need to be dropped and they must not be left in an inconsistent sate. Good marks for IBM that all database within in instance can share the same devices unlike db2 where each database has to use seperate devices. So for db2. you need to guess how much free space to leave PER database and overestimate PER database. With Informix you can leave say 20% free for ALL databases and grow as needed. David. On 02 August 2012 at 02:24 KARL OLIVER <karl.oliver@maf.govt.nz> wrote:> thanks for ideas . I decide to initialise instamce as it is a test one. > Much quicker, already wasted 2 hours > Black mark against IBM for logging drop db when logging is OFF. > It does not make sense > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >