Re: The dreaded long transaction error.

Spetc@aol.com wrote: : We recently had to call informix in order to "fix" our database after a long : transaction error. The logical logs had filled as a result of a "simple" : unload from a large table which did group by's, order by's and also had a : min(unique..) thrown in there for good measure! : The logs couldn't be backed up as the job was still regarded as runnning by : the database (even though it wasn't). When you say the logs couldn't be backed up, are you really trying to say that the logs weren't freed? OnLine should be able to back up the logs in this situation, but because of the open transaction(s), they wouldn't get freed. : A few questions : : 1. Why would the above example even use the logical logs? most likely temp tables as a result of the order and group bys : 2. Is there any way to use software to automatically (and selectively!) abort : a job which is threatening to fill the logs? Yes. OnLine does this already. OnLine should terminate a query that causes the logical logs to exceed the high water mark. The terminated query then begins rolling back all that it has done up to this point, and this rollback is what fills up the remainder of the logical logs. Previous versions of OnLine were shipped with the defaults of LTXHWM and LTXEHWM at 80 and 90. But most versions now ship with the defaults set to 50 and 60 respectively. : 4. Does anyone know what Informix did to free the logs? Yes. Most likely a utility was used to zero out the open transaction count in the most recent checkpoint record. This will allow your system to come on-line, but since the system has been fooled into thinking that there are no open transactions, nothing much happens during fast recovery, so there may be lingering effects as a result of this. This is definitely a last-resort type of option. ---------------------------------------------------------------------------- Bill Glidden Sr. Advanced Support Engineer E-mail: glidden@informix.com Advanced Support (415) 926-6904 Informix Software, Inc. ----------------------------------------------------------------------------