Teradata Spool Space Introduction

What is Teradata Spool Space?

Teradata Spool Space is a workspace that Teradata uses to store temporary result sets and final result sets. Spool Space is only used temporarily and released when it is no longer needed, or the session ends. There are four types of Spool Space, which we will explain at the end of this article.

How can we increase the spool space in Teradata?

It is important always to have a spool reserve. Options to make more Teradata Spool Space available to the system are compressing common values, removing fallback protection, and removing indexes. Finally, there is always the possibility of a system upgrade.

What should I do if my system no longer has enough space?

Here the simple rule applies: it is better to be out of spool than out of permanent space because if there is not enough permanent space available, it will typically be a DML statement, which leads to a rollback. SELECT statements mainly use Spoolspace, and rollbacks are, therefore, rather rare.

How is Teradata Spool Space assigned to a User?

The spool space can be assigned when a user is created:

PERMANENT = 800000000 BYTES,
SPOOL = 200000000 BYTES,
PASSWORD = ******;

The assigned spool space represents the upper limit for this user. The spool space is distributed evenly among all AMPs. teradata spool space

I get the Error Message “No more spool space in user…” although there should be enough space available. Why?

Since the total spool space is divided by the number of AMPs, each AMP has only a fraction of the space available.

If a query is skewed, it can happen that an AMP does not have enough space available, and the request cannot be executed.

As a general rule, the more AMPs a system has, the greater the risk.

How is Spool Space managed internally by Teradata?

It is essential to understand that Teradata never holds data and spool space on one cylinder simultaneously. Therefore, cylinders are released immediately as soon as they are no longer needed as spool space.

How can I find out how much Spool Space my User has?


Why is there even a Teradata Spool Space Limit?

This limit is needed to cancel requests that have a too high consumption of resources. Even if high spool space usage does not always mean a bad query, the correlation between bad queries and high spool space usage is high.

What “no more spool space” Scenarios are there?

One reason might be that there are no more free cylinders on the Teradata system. This must be prevented at all costs. Usually, a separate database is created to “reserve” the space to avoid this.

“No more spool space” can also be caused by too many parallel sessions of the same user.

Finally, “no more spool space” can also be triggered by skewed queries or volatile tables.

What must be considered for NOPI Teradata tables?

NOPI tables are ideal for improving loading times with bulk load utilities. But the transactional insert into statement can create skewed tables as rows AMP locally, which can cause a no more spool space problem. Skew can be avoided by redistribution of the rows with HASH BY RANDOM. The second Explain Plan in the figure below distributes the rows equally to all AMPs (“redistributed randomly to all AMPs”) in Step 1:


Why do I have a “no more spool space” problem after a Teradata upgrade although the query worked before?

This can happen if the number of AMPs is higher after the upgrade, but the disk space has not been increased proportionally. Then there is less disk space available for each AMP. The following query can check the number of AMP:


What is Phantom Spool and Leftover Spool?

For Phantom Spool, the table DBC.DatabaseSpace shows that Spool is in use, but no space on the disk is occupied.
Leftover Spool is the Teradata spool space that remains after a request has ended.

How can Phantom Spool Space be released?

The easiest way (without using the console) is to execute the following procedure:

call SYSLIB.FixCurrentSpace(‘S’, ‘Y’, ‘N’, null, cnt, err);

We can use this procedure to solve phantom spool problems and correct inconsistencies in the DBC.DATABASESPACE table.

4 Types of Teradata Spool Space

  • Intermediate
    Will be released as soon as it is no longer needed. Intermediate Spool Space is required by derived subqueries, for example. The execution plan provides information about when spool space is released.
  • Output
    The Output Spool Space holds the result of an SQL request.
  • Volatile
    They are used for volatile tables.
  • Persistent
    Persistent Spool Space survives a system restart or the crash of a node. It can be defined per session or the whole system for the critical workload.

Solve The Teradata Spool Space Problem – Failure 2646 No more spool space

As already mentioned, “no more spool space” can be triggered by different scenarios. Here are some ideas on how to avoid this problem:

Solve The Teradata Spool Space Problem – Failure 2646 No more spool space

Ensure up-to-date statistics

Current statistics are essential so that the optimizer does not copy unnecessarily large amounts of data between the AMPs.

Choose a good Primary Index

Choose a primary index for your tables that distributes the rows evenly across all AMPs.

Prevent “no more spool space” on system level

Prevent the system from getting into a situation where spool space is no longer available (e.g., through a spool reserve database).

Release Spool Space as soon as possible

Drop Volatile Tables immediately when they are no longer needed.
Often you rely on this to happen automatically at the end of the session.
This unnecessarily blocks spool space (but also cylinders that could be used as permanent space, for example).

Here is a link to the Teradata Spool space documentation:

Buy now at Amazon
  • If you don’t have access to remote console utility and dont have access to primary node then how we can release the phantom spool space ?

    • You can try:

      call SYSLIB.FixCurrentSpace(‘S’, ‘Y’, ‘N’, null, cnt, err);

      Details are here:


      Thanks for the question. I have included it in the article because I think it might be interesting for others as well.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like