DIFFERENT SPACE ERRORS IN TERADATA

Share via:

DIFFERENT SPACE ERRORS

Dear Readers,

In this article, we will discuss DIFFERENT SPACE ERRORS

There are various Errorcodes that can be returned due to insufficient space. Some of them are

2507

2646

2644

Every errorcode has a different meaning and so is their cause and resolution.

Let’s look at these Errorcodes in little detail :

Error 2507 :

  • Errortext : Out of spool space on disk
  • This error occurs when there is “physical” disk space shortage on an AMP to allow an operation to complete.
  • It meansa SQL exceeded physical storage limit before exceeding logical limit imposed by PERM or SPOOL space         allocation for a given database.Or, this indicates that a request for SPOOL space allocation failed due to DBS                 CONTROL File System field CylindersSavedforPERM.
  • When we don’t have enough free cylinders(physical storage),mini-cylpack occurs which can be allocated for spool       data to complete a request.

Resolution :

Reduce spool cylinder usage(reduce physical usage) by tuning SQL

(Or)

If out of SPOOL space, resubmit the request, preferably when there is less activity on the system. If out of

PERM space then reduce permanent space usage by archiving journals or user tables where possible. Check if DBS Control FileSystem field CylindersSavedforPERM is set too high.

Error 2646 :

  • Errortext : No more spool space in %DBID
  • Reasons why the issue occurs :
  • When a user exceeds its assigned “logical” spool limit(AMP level issue).
  • When spool space is not available in the specified database or user to complete the query.
  • When atleast one AMP may have run out ofspool space. ( This usually happens when the request is skewed)
  • The profile that the user is assigned to does not have enough spool space. Teradata uses the profile’s lower spool        space as the max spool space.
  • Since spool space is assigned to a user and not a session,so if many sessions are logged in from one user, the last       request may spool out because another session may use more spool that is running from the same user.
  • It may also be returned when there is wrong space accounting/wrong space information in DBC.DatabaseSpace.

Resolution :

  • If the query needs more spool than the allocated maximum amount of spool to the user, then increase user spool         space.
  • If the query needs more spool than the allocated spool space to the profile, then increase profile spool space.
  • If the query always spools out, then make sure that statistics are up to date.
  • It may also be resolved by running updatespace if it is something to do with wrong space info.

SQL to check user’s spool limit :

SQL to check if  user’s spool space is limited through a profile :

SQL to increase user’s spool space :

SQL to increase profile’s spool space :

Error 2644 :

  • Errortext : No more room in database %DBID.
  • This error occurs only when an attempt is made to increase the amount of space used by a database, and there is        no space left to accommodate the increase.
  • Sometimes, even though there is enough free permanent space in the database, the query may fail with this error.       This happens when an AMP exceeds its allowaced MaxPerm.

Note : The numeric database id may be displayed in place of the database name when the name is not available. This wouldbe the case, for example, when a CREATE DATABASE or CREATE USER statement fails because there is insufficient spacein the new database for a default journal table.

Resolution :

  • Delete unwanted data from tables in the database.
  • Drop some of the tables currently contained in the database.
  • Increase permanent space limit for the database.

SQL to identifyAMP with largest CurrentPerm :

SQL to identify table with largest CurrentPerm on the AMP :

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Add Comment