How to Remove Storage Areas and Data Extents in Progress 9.x, 10.x, 11.x, and 12.x

How to remove extents from a Storage Area?
How to remove a Storage Area from the database structure?
Why can the first extent of an Area not be removed when there are no data?

A database extent can only be removed when the extent is not in use. It can be removed offline with the PROSTRCT REMOVE command as long as the High Water Mark of the Area is below the extent. To remove a Storage Area entirely, the area must have no table, index, lob database objects assigned.

Prior to Progress 9.1B, to remove the Storage Area entirely, all data first needs to be deleted by dropping the database object through the Data Dictionary, before all extents of the Area can be removed from the database structure.

Since Progress 9.1B, the Data from the area is 'deleted' during the PROUTIL truncate area process by simply lowering the Area's High Water Mark, which is significantly faster than having to physically delete the data first. Application Schema objects (tables, indexes, lobs) must still be deleted (e.g. DROP TABLE) or moved out of the Area with PROUTIL tablemove before the first extent (.d1) can finally be removed and hence the Storage Area.

Always ensure a valid database backup before proceeding with structural changes. 

A.   To remove a database extent:

Any Storage Area/Extent type needs to be removed offline, once the before-image file is successfully truncated:

$   proutil <dbname> -C truncate bi
$   prostrct remove <dbname> < d | ai | bi > <Area Name>

• The prostrct remove command will remove the last empty extent of the area.
• This command must be repeated when more area extents need to be removed.
• Data Extents will be removed if they are empty or fail with the following messages:​
  The last extent of area <area name> is in use. (8565); Extent delete rejected. (6961)
• Once all area extents are removed, the entire storage area is removed from the database structure.
• After-Image extents cannot be removed while after-imaging is active.
• Extents cannot be removed from OpenEdge Replication target databases. Refer to Article  How to remove extents from a replication enabled database ?   
• The Schema Area (Area number 6) can neither be removed nor can this area be truncated. Refer to Article Can Proutil Truncate Area be used to delete Schema?   

B.   To empty Storage Areas

When removing a data extent fails, one of the following conditions are not true:

1. An empty Storage Area is defined as an area with no database objects in it (table, index, lob).  
2. An empty extent is defined as an extent below the Storage Area's High Water Mark. 

The PROUTIL truncate area option implemented in Progress 9.1B is designed to fast-delete the entire data content of an area to facilitate removing extents. It is an offline utility that can be run once after-imaging is disabled. For further information refer to Article What does the truncate area utility do?  

The "truncate area" utility is also useful to:

• Delete all records in a table if the table is in an area by itself. Alternatives are discussed in Article  Empty a Database Table without truncate area   
• Enhance the process of loading the data content of one area to a new area, for example refer to Method 1 in Article  How to move tables faster with tablemove   

a) To truncate all empty storage areas:

In Progress 9.0x and 9.1A this is the only functionality. It is useful to remove all empty areas but not as a fast-delete of area content data.

First delete the objects from the Storage Area(s) through the Data Dictionary, since an empty area is defined as an area with no objects in it. For large tables, deleting the data in parallel sessions before removing the schema definitions especially if the table is in a TYPE I Storage Area. Refer to Article  What is the fastest way to remove all records from a database table?   

Then truncate all empty Storage Areas:

b) To truncate specific storage areas:

Since Progress 9.1B, the area name can be specified. The area will be truncated regardless if it contains objects, resulting in all data in that Area's extents no longer being available.  You will be asked for a confirmation before the deletion occurs. The truncate area operation cannot be reverted other than going to backup.

If the tables within the area that is going to be deleted have indexes in other areas, those indexes will be marked as inactive. If associated indexes or tables are in different areas to the area being truncated both areas may need to be truncated prior OpenEdge 10.1C04, 10.2A02, 10.2B. Refer to Article Truncate Area not deactivating all indexes 

1.  Truncate the Storage Area offline
Pay special attention to the confirmation messages:
2.  Rebuild the indexes for the table(s) in the truncated area.

Strictly only the Primary Indexes need to be rebuilt for the table schema to be deleted in the next step.
The indexes to be rebuilt may not neccissarily be in the same area as the truncated area.
3.  Delete the table(s) from the truncated area through:

• The Data Dictionary
• Applying a "DROP TABLE" incremental .df with the Data Administration tool.
• Alternatively move the tables (and indexes if applicable) to another storage area with "PROUTIL -C tablemove"

If this is an encrypted storage area, remove epolicies for the area before removing the area extents from the database structure. Refer to Article:
 How to remove Encryption Policies defined on a Type I area before using prostrct to remove the area?  

4.  Remove all the storage area extents to remove the area from the database structure