Problem:
Query failed with 2646 No more spool space
*** Failure 2646 No more spool space in USER_Analys.
Statement# 1, Info =0
*** Total elapsed time was 25 seconds.
Cause:
You might see Error 2646 No more spool space for left behind spool space. When the DBC.DatabaseSpace table is not properly updated to reflect current and actual space usage that cause the phantom spool. If same query run successfully by allocating more spool space, then, spool is released (Teradata, 2016). Phantom spool may remain after running FastExport job. The current spool remains non-zero even after the user logs off (Teradata, 2018).
Solution:
Check the users have phantom spool space.
SELECT date as Date_exec,Databasename as Username,SUM(currentSpool)/1024/1024 as CurrentSpool_MB
FROM DBC.DiskspaceV
WHERE Databasename NOT IN (SELECT Username FROM DBC.SessionInfov)
and CurrentSpool > 0
group by 1,2;
You can check in detail how much space the phantom spool space occupied in each AMP.
SELECT DATABASENAME, VPROC, CAST(CURRENTSPOOL/1024/1024 as DECIMAL (6,2)),MaxProfileSpool/1024/1024
FROM DBC.DISKSPACE
WHERE DATABASENAME='USER_ANALYS'
AND CURRENTSPOOL > 0
ORDER BY 3 desc;
Now let’s fix it (Teradata, 2016).
SMP001-01:~ # cnsterm 6
Attempting to connect to CNS...Completed.
Hello
Input Supervisor Command:
> start updatespace
start updatespace
Started 'updatespace' in window 1
at Tue May 22 11:57:59 2018
Input Supervisor Command:
> ^C
SMP001-01:~ # cnsterm 1
Attempting to connect to CNS...Completed.
Hello
| / |/ \ ____| ____| ____| | ____|
| --- | / | / | / | | / |
| \___ | \____| \____| \____| |__ \____|
Release 15.10.07.11 Version 15.10.07.11
UPDATE SPACE Utility (Dec 94)
The Update Space program provides for recalculating the permanent
database space or temporary space or spool space used by a single
database or by all databases in the system. The program also
recalculates the temporary or spool space for Trusted Session
proxy users.
The format of the input command is:
UPDATE [SPOOL | TEMPORARY | PSPOOL | ALL] SPACE FOR {ALL [DATABASES | PROXYUSERS] | dbname} ;
Enter either ALL DATABASES or the name of the database for which
database space is to be recalculated. The SPOOL and PSPOOL
parameters are only allowed with a single database.
Enter ALL PROXYUSERS to recalculate space for Trusted Session
proxy users.
Enter Command
> update spool space for USER_Analys;
update spool space for IT_USER_Analys;
Updating space for IT_USER_ANALYS
Space updated for IT_USER_ANALYS
Enter QUIT or CONTINUE.
> CONTINUE
CONTINUE
Enter Command
> update spool space for USER_ADMIN;
update spool space for USER_ADMIN;
Updating space for USER_ADMIN
Space updated for USER_ADMIN
Enter QUIT or CONTINUE.
> QUIT
QUIT
Exiting Updatespace program
^C
Teradata. (2016, 06 16). Retrieved from http://knowledge.teradata.com/KCS/id/KCS001899
Teradata. (2018, 03 27). Retrieved from http://knowledge.teradata.com/KCS/id/KCS011654