Long transaction, logical logs, and temp tables.

[ Well, I guess I'll post this, since I said I would. ] A few weeks ago, we had a minor disaster, from which we were able to fully recover. This posting is a discussion of two issues that came out of it. The short version is: always use the `with no log' clause for your `into temp' selects. Temporary tables: Something we learned the hard way; Select statements can generate transactions. We are aware that long transactions can be a BIG problem if you fill your logical logs. We've been very careful not to do any big updates/inserts or deletes. You wouldn't think that a select statement would have any transactions, but it does if you use a temporary table. Seems obvious now. What this means is that from now on, we will be using the `WITH NO LOG' clause with any queries that select into temp. We don't have any such queries in our production code, but we often do in Ace reports and ad-hoc queries. Logical logs full: When OnLine detects a long transaction, it goes through a series of steps to recover. These are described on page 2-142 of our OnLine Adminstrator's Guide (Version 5, Dec 1991, pn 000-7106). This is in chapter 2, System Architecture, Logical Log Files, Long Transactions in the Logical Log. It mentions that you must do a data restore to recover, and that you MUST NOT roll forward the last logical log file, as doing so recreates the problem. Well ok, but how do you keep from rolling forward the last transaction that caused the problem? Having never done a restore before, we didn't know quite what to expect. We were concerned about stopping the roll forward before the bad transaction got started. When we started the roll forward process, we were prompted to insert the log tape, but never asked where we want to stop rolling forward. A bit of back ground; We use a DAT tape to continuously back up our logical logs. One DAT holds about 1.3 Gig, and one tape lasts about two weeks. This particular tape had been started on the morning of the disaster. It appears that with OnLine 5.0 that DAT tapes were not taken into account, as the only apparent method of stopping the roll forward process is to answer `no' to the `next tape' prompt. Since we have all our logical logs on one tape, there is no way to prevent the last log file from rolling forward, recreating the problem. Sure enough, the roll forward finished, and we were prompted for the next tape, we answered `n' (we didn't have a next tape). OnLine went into recovery mode, and into the same loop that started the whole problem, saything that `Logical Logs Files are Full -- Backup is needed'. I figured that we were in for a very long day (after a very long night), and shut OnLine down. Just to see what happened again, I restarted OnLine. I was expecting to again be stuck in recovery mode, but to our surprise it came up in Quiesent mode. Here is the relevant portion of the log file. ... 05:24:14 Logical Log Files are Full -- Backup is Needed 05:24:15 Logical Log Files are Full -- Backup is Needed Thu Oct 14 05:29:42 1993 05:29:42 INFORMIX-OnLine Initialized -- Shared Memory Initialized 05:29:42 Physical Recovery Started 05:29:44 Checkpoint Completed 05:29:44 Physical Recovery Complete: 80 Pages Restored 05:29:44 Logical Recovery Complete 0 Committed, 1 Rolled Back, 0 Open, 0 Bad Locks 05:29:44 Checkpoint Completed 05:29:50 Dropping temporary tblspace 1000140, recovering 8 pages. 05:29:50 Dropping temporary tblspace 1000141, recovering 8 pages. 05:29:50 Dropping temporary tblspace 1000142, recovering 8 pages. 05:29:50 Dropping temporary tblspace 1000143, recovering 8 pages. 05:29:50 Dropping temporary tblspace 1000144, recovering 8 pages. 05:29:50 Dropping temporary tblspace 1000148, recovering 96 pages. 05:29:50 Dropping temporary tblspace 1000149, recovering 8 pages. 05:29:50 Dropping temporary tblspace 100014b, recovering 2352 pages. 05:29:52 Quiescent Mode ... After talking to technical support at Informix, we learned that there is no way to control which log files will be rolled forward and which won't, based on log number. The only documented method for not rolling forward all logical logs is to have them on different tapes. We did discover one method that will interrupt the the roll forward process; run tblog. I did this while we were halfway through our logs, and suddenly the restore was done. Unfortunately, we hadn't rolled forward enough logs to go back to production, so we had to start the restore proces all over again. However, it seems to me that if you carefully monitored the roll forward process, you might be able to interrupt the process by running tblog at just the right time. -- Rodger Anderson