2646 No more spool space for Phantom Spool space

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

 

Comments