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

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