Question Is It Possible To Get The Free Space Back In An Area?

[catch.saravana]

Progress Version: 11.6

Let's take an example,
1. I have an AREA with variable extent
2. I created couple of tables say 't1' (250MB size) and 't2' (350 MB size)
3. Take a backup of the db
4. Delete the table 't1'
5. Free chain analysis says '888 block(s) found in the free chain.'. Backup size remains the same as that of initial backup.
6. Add a new table 't4' (150 MB size)
7. Free chain analysis says '320 block(s) found in the free chain.'. Backup size remains the same as that of initial backup.

Does progress only reuses the space like step 7? Or is there a way we can make progress to deallocate (on step5) the extents that were created as part of step 2 (meaning like db backup size gets reduced when tables are deleted)?

 

[Rob Fitzpatrick]

This pertains to Type II areas. Type I areas aren't worth discussing.

Variable-size extents grow but never shrink. What happens to blocks within an area depends on the block type and the operations. If you delete records from a table, RM (record) blocks remain as RM blocks, and IX (index) blocks remain as IX blocks, even if empty. The RM blocks are added to the RM chain of the object; the linked list of blocks with usable space to insert records. But those blocks will not be used for other objects. If you compact or rebuild an index, IX blocks can be converted back to free blocks.

On the other hand, if you delete a table rather than deleting data from it, the table and its indexes are no longer in the schema and they no longer have storage meta-data (RM chain, object block, etc.). The clusters of RM and IX blocks that previously were part of the objects are put on the free cluster chain for the area. They will be listed as free blocks in the Block Analysis section for the area in a dbanalysis report.

With more free blocks in your DB for a given physical size, the backup size won't shrink unless you use -com with probkup; -com compresses free blocks to a small fixed size.

The only way to reclaim disk space after removing large objects is to physically reorganize your data, i.e. by a dump and load of the affected area(s) or by moving the remaining objects in the affected area(s) to other areas via proutil tablemove/indexmove and then removing the old area(s).

 

[catch.saravana]

Excellent explanation. Yes Rob, it's Type II areas. Sorry, should have mentioned it before.

The only way to reclaim disk space after removing large objects is to physically reorganize your data, i.e. by a dump and load of the affected area(s)

Quick question - Let's take a case I have 4 tables in an area where t1 is 40GB, t2 is 30 GB, t3 is 60 GB and t4 is 10 GB. Let's say I delete table 't3'. When you say I have to dump and load it's still I have to dump data from the 3 tables (t1, t2, t4) and remove the objects and area. Create a fresh area and load the data back to those tables (t1, t2, t4). Is my understanding right?

 

[Rob Fitzpatrick]

Quick question - Let's take a case I have 4 tables in an area where t1 is 40GB, t2 is 30 GB, t3 is 60 GB and t4 is 10 GB. Let's say I delete table 't3'. When you say I have to dump and load it's still I have to dump data from the 3 tables (t1, t2, t4) and remove the objects and area. Create a fresh area and load the data back to those tables (t1, t2, t4). Is my understanding right?

Yes, if you want to reclaim the 60 GB of disk space that t3 occupied, you would either tablemove t1, t2, and t4 to other areas (not feasible, when the size is in GBs) or dump and load those three tables into other areas. Be sure to rename and retain the old tables and area until the D&L/rebuild is complete, so you have a back-out plan. Once it's done and you delete those old tables and confirm that the old area is logically empty, prostrct remove it.

Or, if you aren't concerned about temporarily using more space than you need to, you could just wait until t1, t2, and t4 collectively grow by 60 GB and then you will have used up all of the free blocks created in that area by deleting t3. (I'm not recommending that you do that; just pointing it out for the sake of completeness.)

 

[catch.saravana]

Got it! Thanks Rob.