Never Again “No More Room In Database”. I’ll Show You How!

Roland Wenzlofsky

January 12, 2019

minutes reading time

With the Teradata Version 16.00, some exciting features in Space Management have been introduced, which can bring enormous improvement in the daily activities in the administration and loading of the data warehouse.

In my current environment, it regularly happens that the data warehouse’s daily loading is interrupted because there is not enough permanent space available in a database.

The maximum available space is defined when we create a database. Below statement shows the syntax to create a database with 80 GB perm space:

CREATE DATABASE Customer AS PERM = 800000000000;

Teradata distributes the entire defined space evenly across all AMPs. For simplicity’s sake, we assume 8 AMPs. In this case, each AMP has 10GB of permanent space available.

Limits for Skewed Tables

The even distribution of the PermSpace over all AMPs is done under the assumption that all table rows are equally distributed over all AMPs. But what happens if this is not the case?

Let’s assume that we want to load a table with 11GB of data, and the designer of the table structure has chosen the Primary Index so clumsy that all rows are assigned to precisely one AMP. Since the space limit on each AMP is 10GB,
we will receive the following error message:

2644: “No more room in database Customer.

Although the remaining 7 AMPs each have 10GB available, Teradata can’t load the table.

The maximum available space in a database we can check on table DBC.DATABASESPACE in the MaxPermSpace column. The currently occupied space is visible in Column CurrentPermSpace. In CurrentPermSpace, however, only the entire occupied space of the database is displayed, not the occupied space per AMP.

I’ve often seen the astonished faces of developers when there was enough space (MaxPermSpace – CurrentPermSpace), and the error message “2644: No more room in database Customer” occurred.

To reduce or completely prevent such problems, Teradata 16.00 offers the possibility to define the available space globally.

Therefore the new DBC Table GlobalDBSpace and an extended syntax for creating a database were introduced:

CREATE DATABASE Customer AS PERM = 800000000000 SKEW = 10 PERCENT;

The new “SKEW = 10 PERCENT” feature is set when creating the database and means the following:

The total PermSpace available is still 80GB. However, each of the eight available AMPs can hold up to 11GB of data if it does not exceed the absolute upper limit of 80GB of the database. It, therefore, defines by how many percent an AMP may exceed its allocated space without the query being aborted.

In our example from above, in which the primary index’s clumsy selection would insert all rows on exactly one AMP, the error “2644 No more room in database Customer” would no longer occur. However, Teradata records such an event in the Software Event Log.

The feature described above simplifies the handling of skewed tables; Additionally, Teradata introduced another feature with Version 16.00, which has the purpose of preventing loads from failing if not enough database space is available:

Global Space Soft Limit.

The Global Space Soft Limit is defined via DBSControl GlobalSpaceSoftLimit.

We define an additional space in percent of the base size that can be occupied without causing the error “2644 No more room in database Customer” occurring.

For example, if we create a database on an 8 AMP system with 80GB PermSpace, and define a soft limit of 10%, up to 11GB PermSpace can be used per AMP. So it is an overall limit that is defined across all AMPs.

An event is triggered and written to the Software Event Log if the database size exceeds the base size. Software event log entries allow the database administrators to analyze the circumstances and take appropriate action.

  • Avatar
    Antti Keski-Kuha says:

    Question about skew = 10 percent: the error “2644 No more room in database Customer” would no longer occur.

    Is it so that you still might get No more room in database. Now you have just 1 gigabytes per AMP more space to follow clumsy primary index?

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like