Query to use to calculate the remaining space in TD system

Database
Teradata Employee

Query to use to calculate the remaining space in TD system

Hello All,

I would like to use a query to find the available disk space in order to calculate the maximum number of databases that can be created in the system.

 

To determine the remaining space available in TD system:

Query1: SELECT SUM(MaxPerm)- SUM(CurrentPerm)(FORMAT 'zzz,zzz,zzz,999') FROM dbc.Diskspace;

 

I want to find out how many databases I will be able to create with the following query:

CREATE DATABASE "DB_A,B..." FROM "DBC" AS PERMANENT = 65000000000, ACCOUNT = 'DBC'FALLBACKNOBEFOREJOURNALNOAFTERJOURNAL;

 

If Query1 returns 9000000000000. Is the number of databases of size 65000000000 be equal to 9000000000000/65000000000?

Thank you


Accepted Solutions
Highlighted
Teradata Employee

Re: Query to use to calculate the remaining space in TD system

No. The new database will be allocated equally across AMPs and will only be taken FROM the unused PermSpace in the specific database stated in the CREATE (or the default database, if not explicitly stated).

 

So in your example, it would be better to use something like:

SELECT MIN(MaxPerm-CurrentPerm)*COUNT(*) (FORMAT 'zzz,zzz,zzz,999') FROM dbc.Diskspace WHERE DatabaseName='DBC';

 

Note that best practice is to allocate all the "customer data space" to some other database at initial setup and leave DBC alone after that. You should leave lots of "free" space in DBC because Transient Journal (WAL) gets charged to DBC in space accounting.

1 ACCEPTED SOLUTION
1 REPLY
Highlighted
Teradata Employee

Re: Query to use to calculate the remaining space in TD system

No. The new database will be allocated equally across AMPs and will only be taken FROM the unused PermSpace in the specific database stated in the CREATE (or the default database, if not explicitly stated).

 

So in your example, it would be better to use something like:

SELECT MIN(MaxPerm-CurrentPerm)*COUNT(*) (FORMAT 'zzz,zzz,zzz,999') FROM dbc.Diskspace WHERE DatabaseName='DBC';

 

Note that best practice is to allocate all the "customer data space" to some other database at initial setup and leave DBC alone after that. You should leave lots of "free" space in DBC because Transient Journal (WAL) gets charged to DBC in space accounting.