We have a table in our DB which has a BLOB on it. The table is in storage area A (not the real name) and the BLOB field is allocated to storage area B. Unfortunately storage area B also has a lot of other stuff in it. Bad design I know. It's on our list of fixes for the future.
We have identified that a LOT of the BLOB records are in fact duplicates and have removed them. Obviously this doesn't actually free up the space the DB uses. So I'm wondering what process I need to follow in order to free up this space on disk? I'm doing a test of the process in my development environment. It should free around 25% of disk usage across the whole DB.
Sorry for the basic question. My DBA experience is very minimal.
Progress version 11.2.1.
How big is the rest of the stuff in Area B? It may be easier to just tablemove or D&L the rest of the stuff then truncate the area.
My additional $0.02CAD: How much space do you expect to recoup? Is it worth the hassle? How long will it take you to fill up that BLOB space with new records? Usually the time/effort cost to recoup disk space is higher than the cost of the associated space.
For the other DBAs listening in: will those newly emptied BLOB blocks show up as free blocks and not get backed up by probkup? I would think so but I'm not certain.
Thanks Paul. The rest of the area is about 16.8GB in physical size, but it's a lot of tables. Seems to be the main dumping ground for stuff.
As for space recouped, we're looking at around 80GB.
In the long run we want to get the BLOBs out of the database completely and into the file system as they're almost half of the DB. But that's for a different day.
80 Gb is not a huge amount. It's hard to justify any time/effort/$$ to save that little space, especially if you expect to pull all the BLOBs out of the DB in the near future.
16.8 Gb across a lot of tables might be mostly doable with tablemove. Or worst case, you tablemove all the relatively small ones and D&L the bigger ones. But really, D&L of 17Gb should only take an hour or two so if you can spare the downtime, I would just do that.
Thanks again Paul. I appreciate that 80GB isn't a massive amount, and in the long run it shouldn't be a big thing, but there are other factors making it seem more interesting. We are in the process of sorting out a DR plan as we are physically moving all our servers a few miles down the road. The reduction in time that these 80GB will give us on a number of aspects are tempting. Also, our servers are creaking at the seams in terms of disk space. And for reasons known only to the management they are refusing to purchase more disk.
Ahhh!! You said the magic word that trumps all logic: "management". :-)
So go ahead and take an hour or two to D&L the 17Gb.