Re: Logging on rollback?

> I have question on logging during a rollback and aborted > long transaction. My understanding was that activity > during a transaction is written to a logical log and if that > transaction is rollbacked, the rollback is also logged. > > Thus, if you have a transaction involving 1 Meg of activity, > then rollback the transaction, there will be approx 2 Megs > of logical logs used. Also, I assume no logs ( involved > with the begin transaction ) can be freed until the rollback is > completed. > > Our config has high-water mark set at 50%. During an alter > table operation ( not by me!), data involved was just over 50% > of avail. logical log space which triggered an abort of long > transaction. By looking at the engine log, I could see that > 18 logs ( of 37 logical logs) were filled, then long > transaction abort. BUT after that, logs weren't filled during > what I assume is a rollback. > > I have less than a year of experience and am no expert so > any help would be appreciated ! I just want some clarification.. > > Thanks, > -Lin This is a very misunderstood topic. (Informix could hold classes in nothing but logging!) The best post I've seen on it was from the incredible June Tong of Informix, reprinted here without permission: : how about 45% and 48%? Even this is overkill. The only way you could need it as low as 50% is if all you were logging was EXTREMELY short log records. You need to log one CLR (Compensation Log Record) for each record logged during the transaction. CLR's are (I think) 12 bytes. Almost every other log record is longer, and most are much longer: INSERTs, for example, contain the row inserted, in addition to a 12 or 16 byte header. So if you're just inserting rows of length 1, and the transaction started at the very beginning of the log (because the whole log counts against you toward LTXHWM, but you don't have to roll back anything that isn't the long transaction) and this was the only user on the system, THEN, you might need close to 50% to roll back. But then you wouldn't need LTXEHWM as low as 48%. If you are INCREDIBLY paranoid (I am sometimes), set them to something like 50% and 65%. But just as important, adjust your total log space so that now 50% of your logs is adequate to COMPLETE your transactions. No point in having everything roll back safely if nothing completes. End reprint. IMHO, the Informix DBA course adds nothing to the Admin manual (covers less, actually), the Informix "Managing Large Databases" course won't help, either. Joe Lumbley's book "Informix Database Administrator's Survival Guide" has about the best advice and interpretation of the logging story. Buy it. Read it. Live it (with a liberal dose of common sense, of course.) Good luck--you'll need it! ;) __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________|