Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-15-2013
01:28 PM

01-15-2013
01:28 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-16-2013
10:06 PM

01-16-2013
10:06 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-17-2013
05:52 AM

01-17-2013
05:52 AM

It worked fine. Thanks a lot !

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-17-2013
12:36 PM

01-17-2013
12:36 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-17-2013
01:42 PM

01-17-2013
01:42 PM

Thank you.

Do you have any suggesion for my second question ?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-17-2013
02:02 PM

01-17-2013
02:02 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-17-2013
02:18 PM

01-17-2013
02:18 PM

That is very helpful. Thanks.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-20-2013
08:35 PM

01-20-2013
08:35 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-21-2013
12:14 AM

01-21-2013
12:14 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-21-2013
12:14 AM

01-21-2013
12:14 AM

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