Answered Truncating before image file, do i need to backup the database first?

progdev1

Member
Hi,
My clients progress (10.2B) database has a bi image approaching 1.5GB. The limit is 2GB. The database extent that has grown is It is a before image file [database name].b2. I am debating whether I should just prostrct add a few new extents or just truncate the bi file. The problem I have is Access to the live system is limited, because the company work until late and start early. They are not quiet 24 hour, but they are close . So if I brought the database down, I'd have to force all the users out of the system for a spell.

I had considered just adding extra bi extents. Looking at the progress knowledge base, they recommend backing up the database first. This makes sense, because you are changing the database. The problem with this is, this would probably take 1 hour to do, so I'd rather avoid if I can.

I know I can truncate the bi file. If I can do this without having to backup the database this might be a faster option. If I could stop the database truncate the bi file this would give me enough time to add more extents at a more opportune time, e.g. the weekend .

I am just wondering if I do need to backup the database before truncating, would anyone know? I have had a look online and I don't see any mention this should be done first. I would assume given I'm not changing the database structure that this is not necessary. Does anyone know if this assumption is correct. Any advice would be appreciated.
 

TomBascom

Curmudgeon
There is no requirement to backup prior to truncating the bi file. In fact it is common to truncate *prior* to backing up when making an offline backup.

There has not been a 2GB bi size limit since version 8.

You can add extents or, if large files are enabled, allow a variable extent to grow as big as it would like.

Just because you can doesn't mean that you *should*. Applications usually "settle in" at a certain amount of bi usage. If the application is reasonably well written and does not suffer from out of control "long open transactions" then the amount of needed bi space is usually quite modest. A few hundred megabytes is usually plenty.

Adding extents is not a very good solution to bi growth - it just hides the problems.

Occasionally even well behaved applications are the victim of bugs and mistakes and their bi files might grow unexpectedly. When this happens you should find the source of the problem, correct it and then truncate the bi file.

It is not normal to see a bi file grow continuously over time - that would be a sign that the application is doing something very strange that involves very large transactions whose scope is constantly growing (or something similar).

One more thing -- if you really want a backup you could do an online backup and avoid the need for downtime. An online backup would be slightly out of date by the time you truncate the bi but if you need to recover you would only need to roll forward a small number of ai extents. (One assumes that you *do* have after-imaging enabled?)
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
The limit is 2GB.
Can you elaborate on that? Do you mean that you don't have large file support enabled and the limit on extent size is 2 GB? Or do you mean you have set a BI threshold of 2 GB (-bithold 2048)? Is the client using the Enterprise RDBMS license?
I am debating whether I should just prostrct add a few new extents or just truncate the bi file.
If the limitation above is due to the use of the BI threshold, adding more BI extents won't change that limitation. Although the BI threshold can be increased online with the proquiet command (Enterprise RDBMS only).

If the license in use is Enterprise RDBMS, there is no reason not to have large file support enabled.
 

progdev1

Member
H Rob & Tomi, thank you both for your replies.
Hi Tom,
It is good to know that I don't need to truncate the database. In the case that the bi file grew suddenly and I need to do work before the weekend, that would be the faster option. You point about the bi file being fairly static and that application causing the bi file to grow is noted.

Hi Rob,
I think they are using Enterprise RDBMS. They are definitely not using enable large files. I am not sure what the license rules are, I was under the impression from speaking to other progress people that the enable large files costs extra and required a additional license. but that doesn't appear to be correct. From what you are saying, if they have Enterprise RDBMS license, the enable large files option is part of this license. Is that correct?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
I think they are using Enterprise RDBMS.
There are a few ways to check.
  • If you have access to Progress Media Status Reports (downloaded from the PSC Customer Order Management site) then that will give you a list of your client's product licenses. You will see either "Enterprise RDBMS" or "Workgroup RDBMS" listed there.
  • You or the client can run "showcfg" in a proenv session. If there are multiple environments (e.g. prod/DR/UAT/test/dev/etc.) then it is a good idea to do this on all of the database servers. Years ago I had a few clients who ran Enterprise in prod and Workgroup elsewhere. That can introduce some operational challenges and it's important to know if you're in that situation.
  • Look at a recent copy of your client's database log, specifically the portion from database startup, between messages (333) and (10471). Some parameter values are Enterprise-specific, e.g. -spin > 1, -lruskips/lru2skips > 0, etc.
They are definitely not using enable large files. I am not sure what the license rules are, I was under the impression from speaking to other progress people that the enable large files costs extra and required a additional license. but that doesn't appear to be correct.
Good news! The "progress people" are wrong. Large file support is a feature of the Enterprise license, not a separate license unto itself. There is no license above Enterprise (though there is an Enterprise license bundle in 11.6 and later: Advanced Enterprise Edition, which is Enterprise RDBMS plus all the add-ons for one price).

From what you are saying, if they have Enterprise RDBMS license, the enable large files option is part of this license. Is that correct?
Correct. All you have to do is proutil dbname -C enablelargefiles when the database is offline. It just takes a few seconds and can be scripted; no backup required. You could do it at the same time as your upcoming BI truncation. E.g.:
Code:
#!/bin/sh
DB=/path/to/my/database
proshut $DB -by
proutil $DB -C enablelargefiles
proutil $DB -C truncate bi
mycustomdbstartup.sh

Do your homework first. Make sure you understand the implications of your change before you make it. I'm just trying to prepare you, not scare you away from making this change. This is something you should do!
  • Make sure you know which RDBMS license is in use in all environments.
    • If Workgroup is in the mix, and some of those Workgroup DBs get refreshed from copies of prod, this is still possible. Look at the -keeptargetlfe parameter on the prorest command (10.2B04+)(link). It will prevent the restored DB from having large files enabled. Workgroup won't start a DB with the Large Files feature enabled.
    • You didn't mention service pack. That's fairly important in 10.2B as it had such a long life and many service packs, several of which added new features. I hope your client is on SP08!
    • If you are in this situation (Ent/WG mix) and prod has an extent > 2 GB, the WG target may need a different structure, with enough extents in that area to hold all the data.
  • Check whether OE Replication/Replication Plus is in use. The target will be affected by what you do to the source.
  • Check whether AI extents from this database are being applied to a hot spare DR database. If so, it should get large files enabled at the same time.
 

progdev1

Member
It is an Enterprise license. Looking at the version of progress on the server it is 10.2B02, I am in the process of moving the client up to 08, but this will take time. I used proutil package -C describe. I don't see any AI extents in the st file so I don't believe that is in use. As there is no no AI I assume that Replication is not in use either.
 
Last edited:

TomBascom

Curmudgeon
While you are running proutil commands you ought to add “proutil dbname -C updatevsts” because the chances are excellent that nobody else has done it.

This will expose all of the updated virtual system tables that have come along since the last time the db was built from scratch. They already exist - updatevsts just makes them visible.
 

cj_brandt

Active Member
There are noticeable performance boosts in 10.2B with SP03 and SP07, so getting to SP08 will be worth the effort.
 
Top