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;
|
Date_exec
|
Username
|
CurrentSpol_MB
|
|
2018-05-18
|
USER_ADMIN
|
0.1181640625
|
|
2018-05-18
|
USER_Analys
|
10960.75390625
|
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;
|
DatabaseName
|
Vporc
|
CurrentSpool(MB)
|
MaxProfilespool(MB)
|
|
USER_Analys
|
51
|
1,949.34
|
4,800.00
|
|
USER_Analys
|
64
|
1,812.85
|
4,800.00
|
|
USER_Analys
|
55
|
951.02
|
4,800.00
|
|
USER_Analys
|
61
|
895.68
|
4,800.00
|
|
USER_Analys
|
54
|
845.45
|
4,800.00
|
|
USER_Analys
|
67
|
712.61
|
4,800.00
|
|
USER_Analys
|
47
|
705.04
|
4,800.00
|
|
USER_Analys
|
41
|
624.63
|
4,800.00
|
|
USER_Analys
|
40
|
514.62
|
4,800.00
|
|
USER_Analys
|
45
|
413.56
|
4,800.00
|
|
USER_Analys
|
53
|
372.60
|
4,800.00
|
|
USER_Analys
|
68
|
327.88
|
4,800.00
|
|
USER_Analys
|
75
|
265.63
|
4,800.00
|
|
USER_Analys
|
79
|
242.92
|
4,800.00
|
|
USER_Analys
|
70
|
223.63
|
4,800.00
|
|
USER_Analys
|
27
|
103.29
|
4,800.00
|
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
References
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