Compact storage area

jdpjamesp

jdpjamesp

ProgressTalk.com Moderator
Staff member
Just a quick question. Pretty sure I know the answer, but I thought I'd best ask :)
We have a table with BLOBS in that is bloating our database. We have worked out that a massive number of these record are duplicates and have worked out a way of removing them. I'm currently testing this, and one of the things I'm interested in seeing is how much space it saves us. I've deleted the physical records in my development environment, but of course that makes no difference to the db size.
Is there a way I can compact a storage area without a dump and load? Also, whilst I'm at it, what is going to be the best way to dump and load? My dev DB is on a USB 2 drive so it's not the quickest! :)

Edit: Progress 11.2.1 on Win7.
 
I don't know of a way to shrink a storage area without a D&L.

Are you looking to move the blobs to a different storage area ? We hit a bug in 10.2x where you must load the blob into the same area number it came from in a dump and load. I don't know if that is fixed in 11.

We moved some BLOB's for performance reasons and we created a copy of the table, used raw-transfer to make a copy of the table and then dropped the original. So the database increased in size before it went back to its original size.
 
Thanks for the answer. We've been advised elsewhere that it's not worth doing what we're planning at the moment for the benefit of 80GB. We have a long-term plan to remove the BLOBs from the database entirely and to put them into the file system instead. he BLOBS currently comprise almost half the db.
 
Truncate area might be useful if by "compact" you mean "eliminate" ;)

The "best" way to dump & load depends on how you are going to measure "bestness".

It also depends on what version of Progress, running on what HW & OS, how your db is put together and how the data is distributed.
 
You can always truncate an area. That will mark it as empty ( as far as I understand it resets the highwater mark for that area ). You can then remove all but the last extent of the area. The last extent of that area can only be removed when you move all schema objects residing in that area to different areas.

Since you can use backup/restore to change the size and numbers of extents for any storage area you could truncate the area, probkup it, edit the .st file so that the concerning area contains just one small extent and - prorest the database.

Heavy Regards, RealHeavyDude.
 
Thanks all for your help. I think we will look at sorting this out once we have moved the BLOBS to the file system, rather than doing it piecemeal.
 
Interesting, it seems to be a continuing trend to remove BLOB's from databases.

Just curious, is it because people are tired of backing up the BLOB data with the db and when the db is restored for testing the BLOB data is there as well ?
 
For us it's the case that the BLOB data is stored on the file system (we receive it via FTP usually), then it is stored as a BLOB. It takes up around 60% of the database at the moment. That data is then backed up every night (we do a nightly full backup). Then we also have that data in UAT and TEST. We also have it on the replication target. In addition the file system is backed up.
This is compounded by the business's reluctance to buy storage. As a result we have vast swathes of data all over the place that could be reduced by putting it in a single location on the file system with relevant security measures in place to stop tampering.
 
I wouldn't say that there is a trend to get rid of the blob data in the database, but I think that we provided the functionality to store blob data ( all sort of documents ) to the users without enough restrictions so that they began to store everything in our application instead of the document archiving system. The reason for it is pretty obvious: Through our application they can easily navigate through them. Therefore we ended up with 25% of a 500 GB database being used by blobs within a year. The data growth for the blobs is not predictable and since our file system space is administered by the GB it already caused the production database to go down.

My 2 cents: Blobs in a database are not a evil thing because of their nature but you need to manage them so that they users are aware that your application is no document archiving system and that the storage of blobs in your database comes with an additional cost.

Heavy Regards, RealHeavyDude.
 
Agree completely. In my opinion they are probably best for stuff that is reasonably static. For example we have a bunch of images we use for reporting. These are rarely added to, but change regularly (about once a year). It's easy to maintain these in a BLOB, but they don't bloat the database unpredictably.
 
You must log in or register to reply here.
Back
Top