Archives of the TeradataForum

Message Posted: Tue, 26 Oct 2004 @ 01:33:12 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

One possibility (although should be unlikely in DBC) is skewing of data allocation.

In a nutshell when you allocate space to a database, the space is evenly allocated to all AMPs. Example a 4 AMP system will get 1GB each if you allocate a database of 4 GB.

The out of space condition will occur when the first AMP reaches its limit.

Continuing the example, the worst case scenario is that if you insert data into a table and find that all records hash to a single AMP. In that scenario, you will only be able to insert 1GB worth of data (filling up one AMP) but the system will still report 3 GB of free space (from the other 3 AMPs that are empty).

You could try running a query like this:

     select databasename, vproc, sum (currentperm) as cp, sum (maxperm) as mp, mp
     - cp as free
     from dbc.allspace
     group by 1,2
     order by 1,2
     where tablename = 'all'

Ideally, the values in the last column ("free") will be about the same (say within about 5% of one another) for any given database. If not, there is some skewing going on.

You can look at individual tables within a database to see which one is skewing the most with something like

     select tablename, vproc, currentperm as cp
     from dbc.allspace
     order by 1,2
     where databasename = 'dbc'

Again tables should have similar amounts of space on each vproc. Especially those with large numbers of rows.

BTW. Journal space (spool and other transient storage) typically comes from free space. A common practice is to allocate a database with an amount of perm, but never use it. This ensures that there is free space for journals and so on. So I doubt that this is your problem - if it was, then you would get an error when running queries that need the journal space but can't get it.

Hope this helps.

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>