2667 Left-over spool table found

Problem:

Query aborted with error 2667 Left-over spool table found. 

Following error in DBQL log.

LOGDATE

QueryID

SessionID

Start Time

ErrorCode

ErrorText

2019-04-17

307181575149842000

10708436

2019-04-17 01:59

2667

Left-over spool table found.

2019-04-17

307181575149875000

10714585

2019-04-17 03:28

2667

Left-over spool table found.

2019-04-17

307181575149926000

10727783

2019-04-17 05:49

2667

Left-over spool table found.

2019-04-17

307181575149946000

10733326

2019-04-17 07:07

2667

Left-over spool table found.

2019-04-17

307181575150004000

10747022

2019-04-17 09:38

2667

Left-over spool table found.

 

Following message, you can see in the /var/log/messages

# psh "grep -B9 -A9 '2667 #Left-over spool table found' /var/log/messages|grep session"

Example Output:

<--------------------- localhost -------------------------------->
Apr 17 13:06:29 SMP001-01 Teradata[10581]: session number: 10558771
Apr 17 13:06:29 SMP001-01 Teradata[11119]: session number: 10558771
Apr 18 07:53:58 SMP001-01 Teradata[11523]: session number: 10521169
Apr 18 07:53:58 SMP001-01 Teradata[11119]: session number: 10521169
Apr 18 08:07:03 SMP001-01 Teradata[10776]: session number: 10558771
Apr 18 08:07:03 SMP001-01 Teradata[10523]: session number: 10558771

....

....

 

Cause:

Spool tables are temporary work tables which are created and dropped as queries are executed. It is the job of the database software - not the query - to drop all the spool tables used during the execution of the query. Leftover spool is a case where - because of a database software bug - a spool table is NOT dropped.

 

Solution:

Please note that there is a distinguish between left over spool from phantom spool:

Left-over spool = spool table is physically left on disk

Phantom spool = spool table no longer exists but DBC.DatabaseSpace table returns spool info “as if” spool still exists

 

There is an internal setting that can be set to delete left over spool. The internal dbscontrol parameter is “Delete Left Over Spool”.  No database restart is needed.  This change will become effective after the DBS Control Record has been written. The setting does NOT prevent left-over spool, but it will be deleted when it is detected.

 

Following way, you can change the parameter. If you have Teradata support, then consult with Teradata Support before doing the change as this is internal parameter.

 

# dbscontrol

 _______

    |                                |              |

    |    ___     __     ____         |    ____    __|__    ____

    |   /      |/  \    ____|    ____|    ____|     |      ____|

    |   ---    |       /    |   /    |   /    |     |     /    |

    |   \___   |       \____|   \____|   \____|     |__   \____|

 

    Release 16.20.24.01 Version 16.20.24.01

    DBSControl Utility (Dec 99)

 

The current DBS Control GDO has been read.

Enter a command, HELP, or QUIT:

mod systemfe=T

The SystemFE field has been modified from FALSE to TRUE.

WARNING: Running DBSControl in SystemFE mode may result in

         inconsistent system behavior!

Are you sure you want to do this (Y/N)?

Y

Enter a command, HELP, or QUIT:

DISPLAY INTERNAL

.

.

.

171.  Delete Left Over Spool = 0

.

.

Enter a command, HELP, or QUIT:

modify internal = true

The Internal field has been modified from FALSE to TRUE.

Enter a command, HELP, or QUIT:

modify internal 171 = 9

The Delete Left Over Spool field has been modified from 0 to 9.

NOTE: This change will become effective after the DBS Control Record

       has been written.

Enter a command, HELP, or QUIT:

modify internal = false

The Internal field has been modified from TRUE to FALSE.

Enter a command, HELP, or QUIT:

mod systemfe=F

The SystemFE field has been modified from TRUE to FALSE.

Enter a command, HELP, or QUIT:

write

Locking the DBS Control GDO...

Updating the DBS Control GDO...

Enter a command, HELP, or QUIT:

QUIT

Exiting DBSControl...

Information about the Parameter “Delete Left Over Spool”

171. DelLeftOverSpool - (Default = 0) This flag is used to enable appropriate actions on a left-over spool or header less leftover spool when encountered.

     The flag is bit-encoded field and controls depending on what bits are set.

     Bit 0 (0000): (Not Set) Abort when left over spool found.

     Bit 0 (0001): (Set) Delete the spool when left over spool found and complete the query processing smoothly. Current txn may be aborted in the event deleting left over spool is not possible.

     Bit 1 (0010): (Set | Unset) This Bit Set or Unset doesn't have any impact.

     Bit 2 (0100): (Set)Check data subtable. If headerless spool found then delete the left over headerless spool and abort the query.

     Bit 3 (1000): (Set)Check data subtable. If headerless spool found then delete the left over data subtable of the spool. The query is allowed to run smoothly.

 

     Note: Bit 0 (set) and Bit 0 (Unset) are complementary to each other in action. Bit 2 and Bit 3 are complementary to each other in action. When both of them are set the Bit 2 will be given preference.

     Actions                                                  Bits to be set.

     =======                                                ===============

     Left Over Spool Found

     *********************

     *********************

     Abort left over spool                                       1. Bit 0 set to false or

                                                                             2. All bits are zero i.e.,DelLeftOverSpool = 0

 

     Delete the left over                                          1. Bit 0 is set to true.

     spool when found and run

     the query smoothly.

 

     Header Less Spool Found

     ***********************

     Delete the header less spool                              1. Bit 2 set to true or

     and abort the query.                                           2. Bit 2 and Bit 3 are both set to true

     Delete on the left-over data                               1. Bit 3 is set to true.

     suitable of the spool and the

     query allowed to run smoothly.

 

     Take snapshot dump when a left over                1. Bit 4 is set to true.

     spool is detected.


If you have issue with Phantom spool space please visit http://www.nazmulhuda.info/2646-no-more-spool-space-for-phantom-spool-space

Comments