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:
- 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.
- Dropping of unwanted Secondary Indexes will result in removing Secondary Index Sub-table hence releasing some much required Storage space.
- 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.
- 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.
- Deleting unwanted Journals may also save some space.
- 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.
- 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.
- 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.
- 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.
- 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.
What will happen if DBC goes out of space
You will start getting query failures because no space left for spool or perm for records.
DBA will ask to drop some unwanted tables , clean datawarehouse , will apply compression on big tables etc .