Recover left over Spool space in Teradata

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);


To clear the same follow the proc below.
1. Login as root in the unix box (NTPA)
2. rlogin (connect to the gateway/active node)
3. cnsterm 6 (open a server session)
4. start updatespace (start the utility)
5. Ctrl+d 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+d to exit the window
10. cnsterm 6
11. stop x (where x is the update space util 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.
> quit