Answered Restoring a database to add extents

[Chris Hughes]

I thought I would try out a theory and see if it would work and it has so I want to run it by the experts and see if there are any downsides.

I wanted an extra extent on a test database, so I edited the .st file adding an extra extent going from

d "I512C":15,1;512 .\mydb_15.d1 f 1048576
d "I512C":15,1;512 .\mydb_15.d2

to

d "I512C":15,1;512 .\mydb_15.d1 f 1048576
d "I512C":15,1;512 .\mydb_15.d2 f 1048576
d "I512C":15,1;512 .\mydb_15.d3

then I restored the database and sure enough my 3 extents (instead of 2) are there.

DB and app appears to work fine.

So that's it, is there any downside other than obvious downtime to backup and restore the database?

Progress 10.2B SP7 (Windows 2008)

 

[RealHeavyDude]

prorest will always use an existing .st file if one exists. If none exists it will create 1 variable extent per storage area in the working directory. If the number and size of extents in the .st file differ from the database the backup was taken then prorest is perfectly okay with that and will create the new structure before it restores the database blocks. I sometimes use backup / restore to consolidate the number of extents in an storage area - from many small to a few big extents for example. How you might wind up with many small extents is another story and beyond this discussion though.

Therfore your strategy works as designed and apart from overcomplicating things there is nothing wrong with your approach. Still, I would not necessarily take that approach to add a storage area or an extent to an existing storage area. prostrct add or prostrct addonline do a perfect job for me because they are designed to do exactly that. All you need is a .st file that contains only what you want to add.

Heavy Regards, RealHeavyDude.

 

[Chris Hughes]

Thanks RHD

It's not my approach as such, I just tried it.

I was always under the belief that prostrct add was the only way, so I like having the additional option now

Also I've never really liked the way that the add process fixes the variable extent size before adding new ones.

Cheers again.

 

[RealHeavyDude]

Just out of curiosity: What is it that you don't like about variable extents being converted into fixed ones?

RealHeavyDude.

 

[Chris Hughes]

Hi

Looking at a structure file of a customer that has been semi-regularly extended, by using the prostrct add the storage area looks similar to

f 512000
f 512000
f 512000
f 35241
f 1048576
f 1048576
f 1048576
f 25612
f 1048576
f 1048576
f 1048576
f 45864
f 1048576
f 1048576
then the variable

I know this doesn't really affect anything as such, I just like the new found option so, as you said, I could restore this db and have 8 2GB extents for example - nice and tidy.

Thanks

Chris.

 

[RealHeavyDude]

I see. You could also define an extent which is variable but may only grow to a limited size ( v instead of f ). The bad thing is that you would have the database hit that limit and shut down to have that extent fixed at the limit size when you add a new one

 

[Rob Fitzpatrick]

Hi

Looking at a structure file of a customer that has been semi-regularly extended, by using the prostrct add the storage area looks similar to

f 512000
f 512000
f 512000
f 35241
f 1048576
f 1048576
f 1048576
f 25612
f 1048576
f 1048576
f 1048576
f 45864
f 1048576
f 1048576
then the variable

I know this doesn't really affect anything as such, I just like the new found option so, as you said, I could restore this db and have 8 2GB extents for example - nice and tidy.

Thanks

Chris.

Is your customer using the workgroup database? If not, it's probably more work than it's worth to manage all those small fixed extents and continually add new ones.

 

[TomBascom]

What Rob is saying is "enable large files and just use a single variable extent per area".

For most people, most of the time, fiddling with managing extents is just wasting time. If your area is growing multiple GB per day it might be worth using some fixed extents for the high growth areas.

 

[Chris Hughes]

Yea - I always enable large files, expect on workgroup of course.

Appreciate what you saying regarding single large variable extent, I've deployed this before as well.

Maybe I'm old fashioned but managing of extents for large customers is part of the service, and I think it's one way of showing the customer you have a close eye on their growth etc.

Thanks all.

 

[Rob Fitzpatrick]

Yea - I always enable large files, expect on workgroup of course.

Appreciate what you saying regarding single large variable extent, I've deployed this before as well.

Maybe I'm old fashioned but managing of extents for large customers is part of the service, and I think it's one way of showing the customer you have a close eye on their growth etc.

Thanks all.

In my experience customers aren't interested in technical minutia and often don't recognize good DB management when they see it. Those who are capable of such distinctions typically manage their own DBs anyway. They do however know when their DB crashes because a fixed extent filled up.

If your large customers don't have so much I/O that they can measure the performance difference between fixed and variable extents then a more complicated structure just makes extra work for you. I suggest you save yourself that effort and use the time gained to add customer value in other ways that are more visible and meaningful to a business user.