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'; |