How to recover left over Spool space in Teradata

The Teradata 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.

• 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:

To Identify the left over spool, use the following SQL.

/* LEFT OVER SPOOL */
sel databasename
,sum(currentspool)
from DBC.DiskSpace
where databasename NOT IN
(sel username from DBC.SessionInfo)
group by 1 having sum(currentspool) > 0
order by 2 desc;

/* Left over spool - by vprocs */
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 above query returns rows, run the Update Space (updatespace) utility to update the spool space accounting in DBC.DatabaseSpace for each of the databases.

If the problem is fixed using Update Space, then the problem was phantom spool. If running Update Space does not fix the problem, you have leftover spool and the only way to fix the problem is to restart the system.

To clear the same follow the below procedure:
1. Login as root in the unix box (NODE)
2. cnsterm 6 (open a suprvsr session)
4. start updatespace (start the utility)
5. Ctrl+c to exit this window
6. cnsterm x (where x is whatever window it's started in - output from the cnsterm 6 screen)

You will see screen with

The format of the input command is:

UPDATE [SPOOL | TEMPORARY | ALL] SPACE FOR {ALL DATABASES | dbname} ;

7. If u decide to do it db by db, then type continue after each update.
8. type quit to exit
9. ctrl+c to exit the window
10. cnsterm 6
11. stop x (where x is the update space utility run window)


Log from the activity ;

Enter Command
> update spool space for myDb;
update spool space for myDb;

Updating space for myDb
Space updated for myDb
Enter QUIT or CONTINUE.
> continue
continue


Enter Command
> update spool space for myDb2;
update spool space for myDb2;

Updating space for myDb2
Space updated for myDb2
Enter QUIT or CONTINUE.