Troubleshoot Spool Space Problems | Teradata Vantage - Troubleshooting Spool Space Problems - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
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 Support.