16.10 - Troubleshooting Spool Space Problems - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Published
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)
Last Update
2018-04-26

The system automatically creates and drops spool files which 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.
  • Phantomspool – 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 Customer Support.