Troubleshoot Spool Space Problems | Teradata Vantage - Troubleshooting Spool Space Problems - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantage™

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.
  • 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 Services.