Teradata No More Room in Database Error and Table Skewness

access_time 22 days ago visibility7 comment 0

No more room in database error

In Teradata, error 'no more room in database' can be common especially when data is not evenly distributed (i.e. high skewness). Sometimes when this error occurs, it might be just because of one or a few AMPs are full.

For example, assuming a database with permanent space allocated as 100GB and there are 100 AMPs. In ideal scenario, each AMP will store 1GB of database for the database. However, in real world, most likely there will be skewness. In the past, I also see scenarios people forgot to specify primary index when creating table and the first column was picked up as PI. If the values are largely same across the whole table, high skewness will occur and also database can run out of space soon.

Check table data distribution on AMPs

As mentioned in article Useful DBC (Data Base Computer) System Views in Teradata, DBC views can be used to check the data distribution of each table.

SELECT  * 
FROM    DBC.TableSizeV 
WHERE DatabaseName='TestDb' AND TableName='test_table';

The above code snippet returns the data distribution of table test_table in TestDb database. 

The following is one example output:

infoColumn Vproc is the AMP identifier. 

Calculate skewness factor

The following query can be used to calculate table skewness:

SELECT TableName,SUM(CurrentPerm) AS CurrentPerm,SUM(PeakPerm) AS PeakPerm,(100 - (AVG(CurrentPerm)/NULLIFZERO(MAX(CurrentPerm))*100)) AS SkewFactor 
FROM Dbc.TableSizeV 
WHERE DatabaseName='TestDb' AND TableName='test_table'
GROUP BY 1

Example output:

	TableName	CurrentPerm	PeakPerm	SkewFactor
1	test_table	3,072	        3,072	        0.00
The skewness of the table is 0 as it is running in a Teradata system with only 2 AMPs and all data is distributed on AMP 0 and also backed-up on AMP 1 (fallback AMP).

Resolve no more room error

There are two common approaches to address this error:

  • Choose a better PI to reduce skewness factor.
  • Allocate more permanent space from parent container if the data is already distributed well (i.e. no better PI candidate).
copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn or Twitter.

Want to publish your article on Kontext?

Learn more

More from Kontext

visibility 160
thumb_up 0
access_time 4 months ago

Teradata has no built-in MD5 function thus custom function needs to be implemented for calculating MD5. This article shows you how to do that using the MD5 message digest UDF provided on Teradata Downloads. Permission CREATE FUNCTION is required for creating UDF in Teradata.  Navigate ...

visibility 7
thumb_up 0
access_time 22 days ago

Teradata hash functions are used to decide data distributions for each row in Teradata tables. When data is inserted into a table, row hash of each record will be calculated first and then the bucket will be calculated, at last the AMP number and fallback AMP number (if fallback is enabled) will be ...

visibility 7
thumb_up 0
access_time 22 days ago

There are two NULLs related Teradata extension to ANSI SQL functions - NULLIFZERO and ZEROIFNULL. This function converts zero to NULL and it is commonly used to avoid error like divide by zeros. SELECT 100/NULLIFZERO(0); Above query returns NULL. And the following query will get one error ...