Use the below sql to find out the remaining space in a Database:

SELECT DatabaseName ,SUM(CurrentPerm)/1024/1024 AS USEDSPACE_IN_MB ,SUM(MaxPerm)/1024/1024 AS MAXSPACE_IN_MB ,SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used ,MAXSPACE_IN_MB- USEDSPACE_IN_MB AS REMAININGSPACE_IN_MB FROM DBC.DiskSpace WHERE DatabaseName = <<DatabaseName>> GROUP BY DatabaseName;

It worked fine. Thanks a lot !

One suggestion:

instead of "SUM(CurrentPerm)" you should consider using "MAX(CurrentPerm) * (HASHAMP()+1)" because this includes wasted perm due to skew. This the minimum perm space needed to store the existing tables.

Dieter

Dieter

Thank you.

Do you have any suggesion for my second question ?

If it's a large number of rows you calculate the average rowsize based on datatypes, add the row overhead (14+ bytes) and multiply times the number of rows.

This is the approximate size of the uncompressed table, after applying the various compression techniques it might be anywhere between 10% and 100% of this size.

In the Databse Design manual "Chapter 14: Database-Level Capacity Planning Considerations" there's a lot about sizing.

Dieter

Dieter

That is very helpful. Thanks.

Dieter,

"MAX(CurrentPerm) * (HASHAMP()+1)" because this includes wasted perm due to skew

Does this mean that even if data is not physically stored, Perm space is wasted due to Skew?

Taking an extreme case scenario: Lets say there are 50 AMPs and there is only 1 table in a Database. And all the rows are on only 1 AMP, occupying 1 MB. Would it waste 1MB per all other 49 AMPs also, causing space occupied to be 50MB instead of expected 1 MB?

Yes thats the correct understanding. If the database has been allocated 50 MB of space on a 50 AMP system, this means each AMP can store max of 1 MB, so if the space on one AMP is exhausted then the database space will also be exhausted.

The used perm space according to dbc.TableSize would still be 1 MB, but the remaining 49 MB in this database can't be assigned to another database, effectively wasting it.

All space (perm(temp/spool) assigned on database level is divided by the number of AMPs, thus each AMP gets exactly the same maximum size.

Dieter

Dieter

Thanks Dieter and Qaisar.

Will this wastage be there at a Table level also?

i.e If I am trying find the Space occpied by a Table (instead of Database) should I use MAX(CurrentPerm) * (HASHAMP()+1) instead of SUM(CurrentPerm) from DBC.TableSize?

Yes, the wastage is at the Table level and thats why the selection of PI is important to avoid skewness.

MAX(CurrentPerm) * (HASHAMP()+1) should give you a more realistic estimates!

Hi All,

Continuing on the above i have come up with couple of queries to evaluate the space utlisation at Database and table level

Could any one evaluate and let me know of any mistakes?

*Database level*

sel

databasename,

sum (currentperm)/(1024*1024) Space_Utilised_MB,

(max(currentperm)* (hashamp()+1))/(1024*1024) as Space_Utilised_Incld_Skew_MB,

sum(maxperm)/(1024*1024) Space_Alloted_MB,

avg(currentperm)/(1024*1024) Avg_Space_Per_Amp_MB,

max(currentperm)/(1024*1024) Max_Space_Amp_MB,

(Space_Alloted_MB - Space_Utilised_MB) Absolut_Space_Left_MB,

(Space_Alloted_MB - Space_Utilised_Incld_Skew_MB ) Space_Left_Consider_Skew_MB,

((1 - (AVG(CURRENTPERM)/MAX(CURRENTPERM)))*100) AS SKEWFACTOR

from

dbc.diskspace

where

databasename = 'XYZ'

group by 1;

**Table level**

sel

databasename,

tablename,

sum (currentperm)/(1024) Space_Utilised_KB,

avg(currentperm)/(1024) Avg_Space_Per_Amp_KB,

max(currentperm)/(1024) Max_Space_Amp_KB,

((1 - (AVG(CURRENTPERM)/MAX(CURRENTPERM)))*100) AS SKEWFACTOR

from

dbc.tablesize

where

databasename = 'XYZ'

group by 1,2;

Regards

R.Rajeev

Hi All,

Could anyone review the above two queries and let me know of any mistakes

Thanks

R.Rajeev

Hi All,

Someone please help me in validating the two queries

Thanks in advance

Regards

R.Rajeev

hi Rajeev,

i dont know about the DB level but for table level use this:

SELECT databasename

, tablename

, MAX(CurrentPerm) * (HASHAMP()+1)/1024/1024/1024 AS Current_Perm

, cast (cast (max (currentperm)*(hashamp()+1) AS float) /

cast (1024*1024*1024 AS float) AS decimal(18,2)) AS Effective_Perm_Including_Skew

, Effective_Perm_Including_Skew - Current_Perm AS Space_Wasted_Due_To_Skew

, (100 - (avg (CurrentPerm) / NULLIFZERO(max (CurrentPerm))*100)) AS Skew_Percent

FROM dbc.tablesize ts

WHERE databasename = 'name of DB'

GROUP by 1,2

ORDER BY 5 DESC

Hi elanir2012,

I believe your query should look like this:

SELECT

DATABASENAME

, TABLENAME

, SUM(CURRENTPERM)/1024/1024/1024 AS CURRENT_PERM

, CAST (CAST (MAX (CURRENTPERM)*(HASHAMP()+1) AS FLOAT) / CAST (1024*1024*1024 AS FLOAT) AS DECIMAL(18,2)) AS EFFECTIVE_PERM_INCLUDING_SKEW

, EFFECTIVE_PERM_INCLUDING_SKEW - CURRENT_PERM AS SPACE_WASTED_DUE_TO_SKEW

, (100 - (AVG (CURRENTPERM) / NULLIFZERO(MAX (CURRENTPERM))*100)) AS SKEW_PERCENT

FROM DBC.TABLESIZE TS

WHERE DATABASENAME = 'XYZ'

GROUP BY 1,2

ORDER BY 6 DESC

changed the expression for current_perm.

Thanks,

-Anjum.

Hi ,

Please help me with this question.

Which two methods are available with Teradata to limit data access? (Choose two.)

A. set a user's PERM space to zero

B. restrict access via database profiles

C. require users to access data via macros

D. require users to access data via stored procedures

I guess the answer should be A, C.

Nt sure.

What happens if the PERM space of any user is zero.

Please suggest.

Thanks

If the perm-space is zero, user usually fly off the planet.

Answer is B & C, as perm-space has nothing to do with data-access rather its for data-storage.

-- If you are stuck at something .... consider it an opportunity to think anew.

A: wrong, most users will have zero Perm Space, this only restricts the possibility to create objects.

B: a profile doesn't specify any access rights, even setting spool to zero will still allow access which doesn't need spool (PI/USI).

C: yes

D: if C is correct, of course this is correct, too.

Dieter

Absolutely correct Dieter .... i was sleepy while mentioning B i guess :)

-- If you are stuck at something .... consider it an opportunity to think anew.

I do not have data in my table still I see the Current Perm on the higher side.

Why is this happening? since there is no data in the table the skew factor is 0.

My table is Multiset table with NUPI defined on it.

Please clarify.....

Thanks in advance...!!!

Sayak G

---- Database level Space Check

SELECT

DatabaseName

,SUM(CurrentPerm)/1024/1024 AS USEDSPACE_IN_MB --- includes wasted perm due to skew

,MAX(CurrentPerm) * (HASHAMP()+1)/1024/1024 AS USEDSPACE_IN_MB_Skew ---includes wasted perm due to skew

,SUM(MaxPerm)/1024/1024 AS MAXSPACE_IN_MB

,SUM(MaxPerm)/1024/1024/1024 AS MAXSPACE_IN_GB

,SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used

,MAXSPACE_IN_MB- USEDSPACE_IN_MB_Skew AS REMAININGSPACE_IN_MB

FROM DBC.DiskSpace

WHERE DatabaseName = 'databasename'

GROUP BY DatabaseName

ORDER BY Percentage_Used DESC

HAVING Percentage_Used IS NOT NULL;

----- Check maximum sized Table

SELECT

tbl.DatabaseName,

tbl.TableName,

tbl.CreatorName AS CREATOR,

SUM(tsize.currentperm) asPermSize,

MAX(tsize.currentperm)/AVG(tsize.currentperm) (DECIMAL (18,2)) (NAMED SkewRatio),

MAX(tsize.currentperm) (NAMED BytesUsedTopAmp)

FROM dbc.tables tbl

INNER JOIN

dbc.tablesize tsize

ON

tbl.Tablename = tsize.Tablename AND tbl.Databasename = tsize.Databasename AND tbl.Tablekind = 'T'

WHERE tbl.databasename IN ('databasename')

GROUP BY 1,2;

--- Tables To delete due to Skewness

SELECT databasename

,tablename

,100 * (1 - (AVG(currentperm) / MAX(currentperm))) skew_factor

FROM dbc.tablesize

WHERE databasename = 'databasename'

GROUP BY 1,2

ORDER BY 3 DESC

--- Table Level Space Check

SELECT

DATABASENAME

, TABLENAME

, SUM(CURRENTPERM)/1024/1024/1024 AS CURRENT_PERM

, CAST (CAST (MAX (CURRENTPERM)*(HASHAMP()+1) AS FLOAT) / CAST (1024*1024*1024 AS FLOAT) AS DECIMAL(18,2)) AS EFFECTIVE_PERM_INCLUDING_SKEW

, EFFECTIVE_PERM_INCLUDING_SKEW - CURRENT_PERM AS SPACE_WASTED_DUE_TO_SKEW

, (100 - (AVG (CURRENTPERM) / NULLIFZERO(MAX (CURRENTPERM))*100)) AS SKEW_PERCENT

FROM DBC.TABLESIZE TS

WHERE DATABASENAME LIKE '<DataBaeName>%' AND TABLENAME = '<TableName>'

GROUP BY 1,2

ORDER BY 6 DESC;

@Sayak, at the time of table creation by default, the header is created on all amps. Though the table does not have any data, the table occupies some currentperm due to the header that was created.

Hi,

I have couple of questions regarding database space.

1) how to find out how much space has been left in an existing database ? I know how to find out the total allocated space to the database but not sure how to find out the remaining space.

2) I have a query which inserts the data into one table. Before I insert, I would like to find out how much space it would occupy in the database once the data gets inserted into the the table.

Thanks.