Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

How to calculate the maximum Storage Area size ?

Information

 
Article Number000010463
EnvironmentProduct: Progress
Product: Openedge
Version: 9.x, 10.x, 11.x
OS: All supported platforms
Question/Problem Description
How to calculate the maximum Storage Area size ?
What is the Progress database maximum Storage Area size limit ?
How big can a Type I Storage Area be ?
How to calculate the Maximum Number of Database Blocks Per Storage Area ?
What is the Maximum data storage area size with a database 4k blocksize?
How to determine if an area is reaching Type I storage area size limits?
Steps to Reproduce
Clarifying Information
Error MessageCreation of this extent would exceed the maximum number of blocks for area <Area Number> prostrct add FAILED. (12867)
Defect/Enhancement Number
Cause
Resolution
For any Progress database prior to OpenEdge 10.1B, before the introduction of 64-bit rowids (2^63 rowids) when used in conjunction with Type II Storage Areas, the Progress Database has a two billion rowid (2^31) maximum per database Storage Area which limits the maximum Storage Area size. 

The maximum size of a Type II Storage Area is fixed at approximately one Petabyte when large files are enabled and maximum sizes are governed by the maximum area size rather than the addressability of 64-bit database keys. In addition:
  • The number of record fragments that can be housed in a database block does not mean that that many records can fit in the block and therefore does not mean that many records will be populated in the area. This is dependent on the blocksize and the record size which leads to 'wasted rowids'. Just because the 'records per block' setting
  • If there are indexes or LOBS as well as tables in the area, the index blocks reduce the maximum number of record fragments that can be housed in an area
  •  All storage areas have reserved space to guarantee space for REDO crash recovery. For further discussion refer to Article:
  • 000044869, When does the 2 billion rowid limit per Storage Area apply?   
In these versions or 10.1B and later versions that still use Type I Storage Areas, the maximum application Storage Area size is determined by three values:
  1. Maximum number of records / record fragments per Storage Area: 2,147,483,647 == 2^31-1
  2. Number of Records Per Block of the Area as defined in the structure file: dbname.st
  3. Database block size
To calculate the maximum 32-bit rowid Storage Area:

1. Calculate the Maximum number of database blocks per Storage Area:

Maximum number of database blocks per Storage Area = Maximum number of records per storage area / number of records per block

2. Use the result to calculate the Maximum Storage Area Size:

Maximum storage area size = Maximum number of database blocks per storage area * Database block size

Example: 

A database uses a Type I Storage Area with:
  • 32 records per block
  • 4K (4096 bytes) database block size.
  1. Maximum number of database blocks per Storage Area = 2,147,483,648 / 32 = 67,108,864
  2. Maximum storage area size = 67,108,864 * 4096 = 274,877,906,944 bytes = 256GB.
The following table provides a Quick Reference lookup of the most common RPB values:
 
Records per Block
Database Blocksize
1
4
8
16
128
512
1,024
32
64
256
512
64
32
128
256
128
16
64
128
256
8
32
64

Using VST's to monitor when a Type I area is reaching the maxarea limit:
 
DEFINE VARIABLE vhiwater AS DECIMAL FORMAT ">>,>>>,>>9" LABEL "Hiwater" NO-UNDO. 
DEFINE VARIABLE vTableAreaRPB AS INTEGER NO-UNDO.
DEFINE VARIABLE vBlockSze AS INTEGER NO-UNDO.
DEFINE VARIABLE vmaxareablx AS INTEGER NO-UNDO.

/* In the event application objects are still in the Schema Area */
FOR EACH _AreaStatus NO-LOCK WHERE _AreaStatus._AreaStatus-Areanum >= 6 
AND NOT _AreaStatus._AreaStatus-Areaname BEGINS "After Image",
/* For mixed Type I + Type II structures, 
we're only interested in Type I Areas for 2 billion rowid maxarea limits */
FIRST _Area WHERE _Area._Area-number =_AreaStatus._AreaStatus-Areanum AND _Area._Area-clustersize = 1 NO-LOCK
BREAK BY _AreaStatus._Areastatus-Areanum.

IF FIRST-OF (_AreaStatus._Areastatus-Areanum)
THEN DO:

ASSIGN /* HWM */
vhiwater = _AreaStatus-Hiwater. 
IF vhiwater = ? THEN vhiwater = 0.0.

ASSIGN /* RPB */
vTableAreaRPB = EXP(2,_Area._Area-recbits).

FIND FIRST _DbStatus. /* Blocksize */
ASSIGN vBlockSze = _DbStatus._DbStatus-DbBlkSize.

ASSIGN vmaxareablx = (EXP(2,31) - 1) / vTableAreaRPB. /* * vBlockSze / 1024 / 1024 / 1024. */

DISPLAY 
_Area-name LABEL 'Storage Area' FORMAT "x(32)" vTableAreaRPB vBlockSze SKIP
vmaxareablx LABEL 'MAXAREA' FORMAT "->,>>>,>>>,>>9"
vhiwater LABEL 'HWM' FORMAT "->,>>>,>>>,>>9"
_AreaStatus._AreaStatus-Hiwater * vTableAreaRPB LABEL "Highest RECID" FORMAT "->,>>>,>>>,>>9".

IF _AreaStatus._AreaStatus-Hiwater / vmaxareablx GE 0.85 THEN DISP "RED ALERT!! ".

END.
END.

 
Workaround
Notes

References to Other Documentation:

Progress Database Administration Guide and Reference, Part I Chapter 3: "Progress Database Limits".
Progress Database Administration Guide and Reference, "Table 3: Maximum Application Data Storage Area Size"

Progress Articles: 

000044869, When does the 2 billion rowid limit per Storage Area apply?   
000013241, What to do when you hit the record limit for a Type I area?    
000010408, Best Practices involving the Schema Area.   
000010613, Best Practices for Managing Records-Per-Block Settings   
000012254, Reserved space used for recovery limits user addressable maximum storage area size.  
000020106, Dbanalys gives contradicting Information about the DB size    


 
Attachment 
Last Modified Date6/3/2019 6:56 AM