Archives of the TeradataForum

Message Posted: Thu, 09 Nov 2006 @ 20:18:49 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Thursday, November 09, 2006 14:56 -->

What is the DR number ?

GSC says this is not a bug.

Fyi ...

Question: What is leftover spool and what can I do about it?

Answer: Spool tables are temporary work tables which are created and dropped as queries are executed. When a query is complete, all the spool tables it used should be dropped. Like all tables, spool tables require a table id. There is a range of tableids exclusively reserved for spool tables (C000 0001 thru FFFF FFFF) and the system cycles through them. If a table is incorrectly not dropped, it remains in existence. Eventually, the system will cycle through all the table ids and reassign the tableid which is in use by our left over spool table. With luck, the presence of this table is detected, the query which was going to use the tableid is aborted -- even though it is innocent of any wrongdoing -- and the following message is returned to it and put in the error log:

*** FAILURE 2667 Left-over spool table found : transaction aborted.

In rare cases, the leftover spool table is not detected and the leftover spool is used. Since it was not created by the current transaction, its format is incorrect and the system will crash in an unpredictable way.

A more subtle condition is when a spool table is dropped, but the steps which reduce the tally of spool space currently in use are not. This is phantom spool. The tallies say the table exists, but it does not. Phantom spool does not cause restarts. Unless the space involved is a significant percentage of the total spool reserve, it is just an annoyance.

The following query can be run to flag the presence of either variety (real or phantom) of leftover spool:

     SELECT DATABASENAME, VPROC, CURRENTSPOOL
     FROM DBC.DISKSPACE
     WHERE DATABASENAME NOT IN (SEL USERNAME FROM DBC.SESSIONINFO)
     AND CURRENTSPOOL > 0
     ORDER BY 1,2 with sum(currentspool);

Should this query return rows, the next step is to run the utility updatespace. This can be done while the system is in operation, but naturally, it is best done during periods of lower usage. Updatespace will correct the phantom spool problem. If the above query still returns rows after updatespace is run, then there are actual leftover spool tables. The way to get rid of them is to perform a database reset. Do not wallow in theory about how to avoid a restart. Trust me. Do a restart and be operational faster and a whole lot surer and safer in the bargain.

An important caveat about the query: notice that the user who caused the leftover spool table to be created must not be logged on when the query is run. If he is, then all spool is considered legitimate by the query. Now, it is not uncommon for some sites to have a user which is nearly always logged on. If the leftover spool was created by such a user, this query will be not report it unless it is run when the system is quiescent.

Filer can also be used by qualified personnel in the GSC to detect real leftover spool on a quiescent system. This often has an advantage of sometimes giving important clues about the root cause.

So what do you do when you discover leftover spool? Institute a procedure to detect leftover spool tables on a regular cycle. You want to discover and eliminate them before the system attempts to reuse the id. How quickly the spool table ids are reused is very site dependent, but something near 2 weeks is the median value.

	<Prev	Next>		<<First	<Prev	Next>	Last>>