The system automatically creates and drops spool files that it uses as temporary work tables to execute queries. Sometimes, one of two potential problems can occur with the spool files:
- Leftover spool – Leftover spool occurs when the system fails to properly drop the spool file after the execution of a query completes. The spool that the query was using appears frozen without a session. This may result in error 2667 which aborts the transaction. Note that with error 2667, the system terminates the query not because the query was faulty, but as a result of the leftover spool problem.
- “Phantom”spool – Phantom spool occurs when the DBC.DatabaseSpace table is not properly updated to reflect current and actual space usage.
To check for either types of spool problems, run the following query when the system is most quiescent and when there are the least amount of users logged on:
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);
If the user whose query unintentionally caused the spool space problem is logged on at the time you run this query, the query will not detect the user because the query is looking for spool usage for a user that is holding spool space but is not logged on.
If the request returns rows, do one of the following things:
- Run the Update Space utility to update the spool space accounting in DBC.DatabaseSpace for each of the databases.
- Run the FixCurrentSpace procedure. This procedure performs the same functions as the UpdateSpace utility. For more information, see The FixCurrentSpace Procedure.
For assistance with spool space problems, contact Teradata Support.