Teradata storage space for beginners

Storage space is used to store data as well as metadata into physical disks. Some data is persistent which remains in disks even when session is over while some may be cleared once application session completes.

TERADATA has three kinds of Storage Space namely PERMANENT, SPOOL and TEMPORARY.

Permanent Space

Permanent Space is used for storing permanent date like Permanent Tables, Secondary Indexes and Permanent Journals. So whenever a table is created and rows inserted into it , it occupies Permanent Space and whenever data is deleted or database objects like tables, indexes are dropped Permanent Space is released. Permanent Space is allocated at the time of creating USER/DATABASE.

SPOOL SPACE

Spool Space is all the space available or we can say un-occupied Permanent Space is called SPOOL Space. Spool Space is used for carrying out all the intermediary SQL operations like creating derived tables or performing some aggregations or storing result set of joins of tables etc. Whenever a SQL query exceeds the SPOOL Space available query is aborted. DBA can limit spool space allocated to each user during new user creation or can modify it at any time later as well.

Temporary Space

Temporary Space is used for storing GLOBAL TEMPORARY TABLES in Teradata. For such tables ,table definition is stored however the table data is truncated once the session is over. Temp space is also un-used Permanent Space.

Understanding Teradata Storage Space is very essential especially when doing Capacity Planning or Volumetric Estimation in Data Warehouse Environment. Sometimes, it may happen that SQL queries may require more SPOOL SPACE for SQL Operations than available. In such situations knowledge about Space may help.

We may come across situation in which we may be encountering errors related with non availability of Storage Space in Teradata. For example:

“NO MORE ROOM IN DATABASE”

or

“NO MORE SPOOL SPACE IN DATABASE”

So what one should do in such cases? We are talking about situations when we really have Storage Space shortage in our Teradata Environment. Adding more physical Storage Space is not the best option available all the time. So what one must do in order to free some existing occupied space ?

Few possible methods of releasing some occupied space:

1. Implementing Single Value or Multi-Value Compression is considered as a very good practice for occupying much less space for same amount of data. In some cases, it can really show huge space saving.
2. Dropping of unwanted Secondary Indexes will result in removing Secondary Index Sub-table hence releasing some much required Storage space.
3. Purging of some DBC tables like ACCESSLOG or DBQL tables may free some space. These tables should not hold data for infinite period. Some purging strategy should be followed for these tables.
4. Try identifying intermediary work tables and deleting them. If the Work Tables are used only for intermediary calculations then implement purging strategy for such tables once the work is done.
5. Deleting unwanted Journals may also save some space.
6. If the tables are created with FALLBACK option and the data is not that critical and can be build again in DataWareHouse , then change the tables to NO FALLBACK. This will help in saving huge amount of data as it requires twice the table size with FALLBACK option enabled.
7. If you are facing issues due to non-availability of SPOOL Space, then try optimising your SQL queries , remove any PRODUCT JOIN and collect stats on columns participating in Joins conditions. Implementing Compression may also help in overcoming SPOOL Space error.
8. Sometimes we create back-up of tables before doing any table loading in order to verify the data with old back up tables. If data is correct and we don’t need back-up tables then such tables should be dropped.
9. If there are some tables which are not used anymore , try Archiving them ; move such tables to other inexpensive Disk hence saving some space in actual disk.
10. If nothing is working for you, consult your DBA for increasing the Space allocated to any Database.

Which method you prefer generally and why for making some space in Teradata database? If I miss any method here, feel free to add by leaving a comment.

---