2

I am trying to drop a varchar(100) column of a 150 GB table (4.6 billion records). All the data in this column is null. I have 30GB more space in the database.

When I attempt to drop the column, it says "no more room in database XY". Why does such an action needs so much space?

ChrisCamp
  • 672
  • 1
  • 5
  • 20
Kornél Regius
  • 2,989
  • 5
  • 30
  • 39

2 Answers2

1

The ALTER TABLE statement needs a temporary storage for the altered version before overwriting the original table. I guess the the table that you are trying to alter occupies at least 1/3 of your total storage size

ponthu
  • 311
  • 1
  • 3
  • 14
0

This could happen for a variety of reasons. It's possible that one of the AMP's in your database are full, this would cause that error even with a minor table alteration.

try running the following SQL to check space 

select VProc, CurrentPerm, MaxPerm 
from dbc.DiskSpace 
where DatabaseName='XY';

also, you should check to see what column your primary index is on in this very large table. if the table is not skewed properly, you could also run into space issues when trying to alter a table or by running a query against it.

For additional suggestions I found a decent article on the kind of things you may want to investigate when the "no more room in database" error occurs - Teradata SQL Tutorial. Some of the suggestions include:

  • dropping any intermediary work or "sandbox" tables
  • implementing single value or multi-value compression.
  • dropping unwanted/unnecessary secondary indexes
  • removing data in dbc tables like accesslog or dbql tables
  • remove and archive old tables that are no longer used.
ChrisCamp
  • 672
  • 1
  • 5
  • 20