How to monitor Database Diskspace Usage

Database

How to monitor Database Diskspace Usage

Hello Guys and Gals,

Just two quick questions..easy ones though.

1. How to monitor the database diskspace usage in teradata. I am expecting a query that would tell me the allocated space, usage, free space in mega bytes.

2. We ran out of space on one of our databases. Can you please tell me how to increase the database space manually through teradata administrator? Is increasing the database space possible through query?

Thanks in advance.

Regards
Sanjay
3 REPLIES
Enthusiast

Re: How to monitor Database Diskspace Usage

we use the below query and you can modify according to your needs.

select databasename,cast(sum(maxperm)/1024/1024/1024 as decimal(7,2)) max_perm,cast(sum(currentperm)/1024/1024/1024 as decimal(7,2)) current_perm,cast(sum(maxspool)/1024/1024/1024 as decimal(7,2)) max_spool,cast(sum(currentspool)/1024/1024/1024 as decimal(7,2)) current_spool from dbc.diskspace where databasename in
(select databasename from dbc.databases where dbkind = 'D'
and databasename in
('STG_DEV', 'STG_TST_TMP', 'WRK_STG_DEV') )
group by databasename order by max_perm desc;

Hope this helps.

Thanks,
M
Enthusiast

Re: How to monitor Database Diskspace Usage

First, your database may continue to show there is space available even though an attempt to insert records into a table fails with inadequate space. The first AMP to reach its perm limit will result in the database reporting it was out of space. This is the result of data distributing unevenly across the AMPs and may indicate a skew problem with the Primary Index.

That being said, you can add space to a database in a multitude of ways. Here is the method I use most often:

CREATE DATABASE moveperm FROM AS PERM = ;
GIVE moveperm TO ;
DROP DATABASE moveperm;

When you drop the database 'moveperm' the space will automatically be assigned to the database's owner. In this case, the database that needs additional perm space.
Teradata Employee

Re: How to monitor Database Diskspace Usage

Space management can also be done through Teradata Manager (at least through Teradata 13.0) or better yet ... Teradata Viewpoint's Teradata Management Portlets offers the "Space Usage" portlet.