2646 No more spool space in %DBID in Teradata

Problem:

Query failed with Error code:  2646 No more spool space in %DBID 

 

Cause:

The following reason the 2646 error can occur:

1.     The user spool reaches the maximum limit allocated for the user. The following reason the spool might reach the maximum limit

1.1.  The user may have insufficient spool space to run a query

1.2.  Spool space allocated to user so if multiple people sign in on using same user ID and running sql that takes spool then the last request may run out of spool space because of excessive request of another session using the same User ID.

1.3.  Phantom Spool space (When the DBC.DatabaseSpace table is not properly updated to reflect current and actual space usage that cause the phantom spool)

2.     Spool has been exhausted on a single AMP caused by an extremely skewed query. Following reason, the query can be extremely skewed:

2.1.  If a query become skewed recently and taking lots of spool space but was not skewed earlier then it can be for not to have updated statistics. Check the statistics are up to date

2.2.  The query can be skewed usually for following reason:

2.2.1.     Skewed table e.g Bad choice of PI data is skewed

2.2.2.     Bad execution plan

2.2.3.     Bad data model (normalization, data types, PI etc)

2.2.4.     Too many joins

2.2.5.     Hash Collision

 

Solution:

The following way you can start investigation of the root cause of problem:

Check if the user reaches the maximum spool limit from the Peak Spool and maximum spool information.

 SELECT DataBaseName (title 'UserName', format 'x(20)')

,CAST(SUM(MaxSpool)/1024.00/1024.00/1024.00 as DECIMAL(6,2)) as MAX_SPOOL_GB

,CAST(SUM(PeakSpool)/1024.00/1024.00/1024.00 as DECIMAL(6,2)) as Peak_Spool_GB

,CAST(AVG(PeakSpool)/1024.00/1024.00/1024.00 as DECIMAL(6,2)) as AMP_AAVG_GB

,CAST(MIN(PeakSpool)/1024.00/1024.00/1024.00 as DECIMAL(6,2)) as AMP_MIN_GB

,CAST(MAX(PeakSpool)/1024.00/1024.00/1024.00 as DECIMAL(6,2)) as AMP_MAX_GB

,(100 - (AVG(PeakSpool)/nullifzero(MAX(PeakSpool)*100)))(format 'zzz,zzz,999', title 'SkewFactor')

FROM Dbc.DiskSpace

GROUP BY 1 ORDER BY 1

WHERE DataBaseName = '<User name>';

 

Example Output: 

UserName                      MAX_SPOOL_GB  Peak_Spool_GB  AMP_AAVG_GB  AMP_MIN_GB  AMP_MAX_GB   SkewFactor

-----------------------------------------------------------------------------------------------------------------------------------------------------------

USER_00001                    5120.00            906.42                          3.78                       3.53                   21.33         99.99822974789029

 

If the peak spool reaches the maximum limit, then increase the spool limit of the user and if the peak spool is much lower than the maximum spool then checks the skew factor. If the skew factor is high, then query is failing for skewness.


Check if the query failing for Phantom spool space or not. To get more information please visit the following link:

http://www.nazmulhuda.info/2646-no-more-spool-space-for-phantom-spool-space

Before you decide to increase the spool space if the user peak spool space reaches the maximum limit, check if there are multiple people sing in on using same user and takes spool by requesting excessive request using the following query

 SELECT othq.*

FROM

(

           SELECT *

           FROM PDCRINFO.DBQLogTbl_Hst

            WHERE QueryID = <Query ID> --Query ID of Failed Spool query

--          and ErrorCode=2646

            and logdate='2019-03-05'  -- Date 

) as spq

INNER JOIN PDCRINFO.DBQLogTbl_Hst as othq

ON          spq.LogDate = othq.LogDate

AND       spq.Username = othq.Username

AND spq.SessionID <> othq.SessionID

AND spq.StartTime < spq.LastRespTime

AND othq.StartTime < othq.LastRespTime

AND PERIOD(othq.StartTime,othq.LastRespTime) OVERLAPS PERIOD(spq.StartTime,spq.LastRespTime) 

AND othq.AMPCPUTime > 1000

;

If the user peak spool space reaches the maximum limit and it’s not because of excessive request or Phantom Spool space or skewness, then following way you can increase the spool limit for the user:

 # Run the following query to check whether the user has spool space limited through a profile:

SELECT vproc,maxspool,maxprofilespool,currentspool,peakspool

FROM dbc.diskspace

WHERE databasename = '<database or user name>'

WITH sum(maxspool), sum(maxprofilespool), sum(currentspool), sum(peakspool)

ORDER BY 1 ;

 

# To increase user’s spool space
MODIFY USER <user-name> AS SPOOL = xxxxx BYTES ;
 

# To increase profile’s spool space
MODIFY PROFILE <profile-name> AS SPOOL = xxxxx BYTES;

 

Note: If you increase user spool space but the user using profile then the spool space will be override by the value that configured in the profile.

 If the query is skewed check the following way if its for insufficient stats collection or not. If its skewed for insufficient stats collection then collect the statistics.

diagnostic helpstats on for session;

explain

<your query>;

 If the query has good statistics but it is skewed or taking lots of spool space, then identify the root cause by examining the explain plan of the query and tune it. 

Comments