Calculate Teradata Database Size

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

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