Troubleshooting Spool issues

I recently had a backup failure because of a ‘left-over spool’.

This particular spool problem occurs when a query completes but the Database engine is not able to remove to spool space used because of a bug.

In this case we get this kind of message

*** Failure 2667: Left-over spool table found: transaction aborted

2 kinds of spool issues:

  • A phantom spool that means a mismatch between the space occupied by a spool for a user and the value found in dbc.diskspace. A phantom spool does not have an real existence on the disk
  • A left-over spool that is a real table spool existing on the disk, or/and with a header missing.

How to find the mismatching spool space

In SQL Assistant, play the following query against your system:

SELECT DATABASENAME, SUM(CURRENTSPOOL) AS LEFTOVER_SIZE
FROM DBC.DISKSPACE
WHERE DATABASENAME NOT IN
(
SELECT USERNAME FROM DBC.SESSIONINFO
)
AND CURRENTSPOOL > 0
GROUP BY 1
ORDER BY 1,2
WITH SUM(CURRENTSPOOL)

Get the databasename involved in the spool space problem, let says DB1

Updating spool space

  • Connect to PDN Teradata node
  • Type cnsterm 6
  • In the new windows type start updatespace
  • This command starts the updatespace utility and give you back a windows number (ie Started ‘updatespace’ in window 3)
  • CTRL + C to exit the windows
  • Type cnsterm 3 (or other number regarding the sent back message)
  • Type update spool space for DB1; (where DB1 is the database name you got previously) – don’t forget the comma at the end!

Controling

  • Play again the query to control the updated space and check if you still have a phantom spool

If no rows are returned, then you were facing a phantom space issue: everything is OK now.

If some rows are returns, then you are facng a left-over spool problem

In this case, you must restart your RDBMS, or call your Teradata support: in some cases they can remove the left-over spool table without restarting the machine.

Stoping updatespace utility

Never forget to stop the utility you launch

  • Type quit

When you get Exiting Updatespace program, then press CTRL + C to go back to the original windows

Troubleshooting cnsterm problems

In some cases, if you forget to stop the utilities you launch you can face the following message when you try to launch the updatespace utility:

CNSSUP start: All interactive Partitions are busy!!

You will have to connect to all cnsterm and type quit (or another command sometimes) to clear everything and being able to connect properly to a cnsterm to perform your administration task