Calculate Teradata Database Size

Raymond Raymond event 2021-04-01 visibility 1,879
more_vert

In Teradata, data is stored on AMPs based on primary index.  When calculating space usage on Teradata, we need to calculate all the storage allocation on all the AMPs.

Use DBC.DiskSpaceV[X]

This view can be used to check space usage of any account, database or user.

Example:

SELECT Vproc, DatabaseName,AccountName, MaxPerm, MaxSpool, MaxTemp, CurrentPerm, CurrentSpool FROM DBC.DiskSpaceV
WHERE DatabaseName='TestDb';

Sample output:

	Vproc	DatabaseName	AccountName	MaxPerm	MaxSpool	MaxTemp	CurrentPerm	CurrentSpool
1	0	TestDb	DBC	500,000	19,800,327,618	19,800,327,618	129,536	0
2	1	TestDb	DBC	500,000	19,800,327,618	19,800,327,618	129,536	0
*The unit is bytes.
To calculate the total space, use the following query:
SELECT DatabaseName,SUM(CurrentPerm) AS CurrentPermTotal, SUM(MaxPerm) AS MaxPermTotal FROM DBC.DiskSpaceV
WHERE DatabaseName='TestDb'
GROUP BY DatabaseName;

Sample output:

	DatabaseName	CurrentPermTotal	MaxPermTotal
1	TestDb	259,072	1,000,000

Calculate space utilization rate

The following query can be used to calculate space utilization rate:

SELECT DatabaseName,SUM(CurrentPerm) AS CurrentPermTotal, SUM(MaxPerm) AS MaxPermTotal,
((SUM(CurrentPerm))/ NULLIFZERO(SUM(MaxPerm)*1.0000) * 100)(FORMAT 'zz9.99%', TITLE 'Percent Used')
FROM DBC.DiskSpaceV
WHERE DatabaseName='TestDb'
GROUP BY DatabaseName;

Sample output:

	DatabaseName	CurrentPermTotal	MaxPermTotal	Percent Used
1	TestDb	259,072	1,000,000	25.9100

Use DBC.GlobalDBSpaceV[X]

We can use this view to report database level information that is aggregated from AMPs.

SELECT DatabaseName, MaxPerm, MaxSpool, MaxTemp, AllocatedPerm, MaxPerm-AllocatedPerm (TITLE 'Unallocated') FROM DBC.GlobalDBSpaceV
WHERE DatabaseName='TestDb';

Sample output:

	DatabaseName	MaxPerm	MaxSpool	MaxTemp	AllocatedPerm	Unallocated
1	TestDb	1,000,000	39,600,655,236	39,600,655,236	1,000,000	0

More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts