Check System/Database/Table/Query Space | Teradata Vantage - 2.2 - SQL Requests for Checking Space - Teradata Vantage

Configuring Teradata Vantageā„¢ After Installation

Product
Teradata Vantage
Release Number
2.2
Release Date
January 2021
Content Type
Configuration
Implementation
Publication ID
B700-4014-011K
Language
English (United States)

From a client application, such as BTEQ or Teradata Studio, run the following optional queries to check space.

In BTEQ, to make sure you can see all result columns, run these commands before you run the SQL requests:
.sidetitles on
.foldline on ALL
To check... Run this request...
Total available disk space in the system (MaxPerm)
SELECT SUM(MaxPerm) FROM DBC.DiskSpaceV;
Maximum database space (MaxPerm)
SELECT SUM(t3.Maxperm) FROM
(SELECT
t1.DatabaseName
,t1.Permspace AS Maxperm
,ZEROIFNULL(SUM(t2.CurrentPerm)) AS Currentperm
FROM dbc.databases t1
LEFT OUTER JOIN dbc.tablesize t2
ON (t1.databasename=t2.databasename)
WHERE (Maxperm > 0)
GROUP BY 1,2) t3
;
Database space: maximum available and currently in use
Select
t1.DatabaseName
,t1.Permspace AS Maxperm
,ZEROIFNULL(sum(t2.CurrentPerm)) AS Currentperm
FROM dbc.databases t1
LEFT OUTER JOIN dbc.tablesize t2
ON (t1.databasename=t2.databasename)
WHERE (Maxperm > 0)
GROUP BY 1,2
ORDER BY 1;
Disk space currently in use
SELECT SUM(CurrentPerm),
SUM(MaxPerm),
((Cast(Sum(Currentperm) as float) /
NullifZero(Cast(Sum(maxperm) as float))
*100))
(Title '%MaxPerm', Format 'z z9.99')
FROM DBC.DiskSpaceV;
Spool space on active queries
Select
Vproc
,DatabaseName
,MaxSpool
,CurrentSpool
,PeakSpool
from dbc.diskspace
where currentspool > 0
order by 2,1
;
Users who are running out of permanent space
SELECT Databasename (format 'X(12)')
,SUM(maxperm)
,SUM(currentperm)
,((cast(SUM(currentperm) as float))/
NULLIFZERO (cast(SUM(maxperm) as float)) * 100)
(format 'zz9.99%', TITLE 'Percent // Used')
FROM DBC.DiskSpaceV
GROUP BY 1 HAVING (cast(SUM(currentperm) as float) /
NULLIFZERO (cast(SUM(maxperm) as float)))
 > 0.9
ORDER BY 4 DESC;
You can change the value 0.9 to whatever threshold ratio is appropriate for your site.
Percentage of space used by each database in the system
SELECT Databasename (format 'X(12)')
,SUM(maxperm)
,SUM(currentperm)
,((cast(SUM(currentperm) as float))/
NULLIFZERO (cast(SUM(maxperm) as float)) * 100)
(FORMAT 'zz9.99%', TITLE 'Percent // Used')
FROM DBC.DiskSpaceV
GROUP BY 1
ORDER BY 4 DESC
WITH SUM (currentperm), SUM(maxperm);
Users who are using excessive spool
SELECT databasename
,SUM(peakspool)
FROM DBC.DiskSpaceV
GROUP BY 1 HAVING SUM(peakspool) > 5000000000
ORDER BY 2 DESC;
You can change the value 500000000 to whatever value is appropriate for your site. Some sites with more space may have a greater tolerance for higher spool usage and spool limits.
Unallocated space remaining in the database
SELECT databasename, allocatedperm, maxperm, permskew, maxperm-allocatedperm (TITLE 'UnallocatedSpace') FROM DBC.GlobalDBSpaceV;
Maximum allocated peak spool for a given user
SELECT databasename, PeakAllocatedSpool from DBC.GlobalDBSpaceV;
Disk space for a given table
SELECT SUM(currentperm)
FROM DBC.tablesizeV
WHERE databasename='xxx'
AND tablename = 'xxxx';