Space in the database

Database

Space in the database

Hi,

I have couple of questions regarding database space.

1) how to find out how much space has been left in an existing database ? I know how to find out the total allocated space to the database but not sure how to find out the remaining space.

2) I have a query which inserts the data into one table. Before I insert, I would like to find out how much space it would occupy in the database once the data gets inserted into the the table.

Thanks.

27 REPLIES

Re: Space in the database

Use the below sql to find out the remaining space in a Database:

SELECT 
DatabaseName
,SUM(CurrentPerm)/1024/1024 AS USEDSPACE_IN_MB
,SUM(MaxPerm)/1024/1024 AS MAXSPACE_IN_MB
,SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used
,MAXSPACE_IN_MB- USEDSPACE_IN_MB AS REMAININGSPACE_IN_MB
FROM DBC.DiskSpace
WHERE DatabaseName = <<DatabaseName>>
GROUP BY DatabaseName;

Re: Space in the database

It worked fine. Thanks a lot !

N/A

Re: Space in the database

One suggestion:

instead of "SUM(CurrentPerm)" you should consider using "MAX(CurrentPerm) * (HASHAMP()+1)" because this includes wasted perm due to skew. This the minimum perm space needed to store the existing tables.

Dieter

Re: Space in the database

Thank you.

Do you have any suggesion for my second question ?

N/A

Re: Space in the database

If it's a large number of rows you calculate the average rowsize based on datatypes, add the row overhead (14+ bytes) and multiply times the number of rows.

This is the approximate size of the uncompressed table, after applying the various compression techniques it might be anywhere between 10% and 100% of this size.

In the Databse Design manual "Chapter 14: Database-Level Capacity Planning Considerations" there's a lot about sizing.

Dieter

Re: Space in the database

That is very helpful. Thanks.

Re: Space in the database

Dieter,

"MAX(CurrentPerm) * (HASHAMP()+1)" because this includes wasted perm due to skew

Does this mean that even if data is not physically stored, Perm space is wasted due to Skew?

Taking an extreme case scenario: Lets say there are 50 AMPs and there is only 1 table in a Database. And all the rows are on only 1 AMP, occupying 1 MB. Would it waste 1MB per all other 49 AMPs also, causing space occupied to be 50MB instead of expected 1 MB?

Re: Space in the database

Yes thats the correct understanding. If the database has been allocated 50 MB of space on a 50 AMP system, this means each AMP can store max of 1 MB, so if the space on one AMP is exhausted then the database space will also be exhausted.

N/A

Re: Space in the database

The used perm space according to dbc.TableSize would still be 1 MB, but the remaining 49 MB in this database can't be assigned to another database, effectively wasting it.

All space (perm(temp/spool) assigned on database level is divided by the number of AMPs, thus each AMP gets exactly the same maximum size.

Dieter