By
First a quick summary on what the BI file is and how it works:
Your database Before-Image file plays an important role in ensuring database integrity. It stores information about every transaction so that it can effectively undo or redo all transactions that did not get written to the database.
The BI file is organized into clusters that get filled with pending database change. Under normal circumstances, transactions get committed or rolled back and the clusters get reused. However, when there are no more available clusters left and the database must record a modification, a new BI cluster gets created and your BI file starts to grow.
Many Progress sites have experienced excessive BI growth at least one time, especially those running certain well known manufacturing applications.
There are several causes such as a large transaction scope, a data load or looping application code. The most common cause though is a long transaction and the result can be disastrous. If undetected, the BI will keep growing, and growing, and growing, until the file system fills up and the database crashes.
In previous Progress versions, this was often a “restore from backup” scenario. With newer versions though, you can add additional space after the crash and attempt to restart. However, as the database starts up and goes through its crash-recovery phase, you can wait for hours if your BI had grown to a tremendous size. And if that wasn’t painful enough, the BI file can grow to twice its original size before the crash as it goes through this phase. For example, if your BI file grew to 100GB before the crash, you may need to allocate an additional 100GB of free space in order to be able to use your database again.
So, what to do to prevent this…?
Step 1
Monitor the actual BI file usage. Because the BI clusters get re-used, looking at the BI file size is ineffective. For example, the BI file below is 20GB, however, we cannot tell how much of it is really used:
-rw-r--r-- 1 mfg qad 21574836480 May 17 07:18 mfgprod.b1
At the time of this writing, Progress does not provide any means or indication of how much of the BI is being used, other than when it reaches 90% usage. With ProTop, we can determine both how many clusters and how much of the BI file’s size is being used. This metric below (NumBI) is available in ProTop’s free online dashboard:
We can see in the graph above that the actual BI space used is about 10GB, while the BI file size can be well above that, 20GB for example, and impossible to tell by only looking at the BI’s file size. ProTop goes beyond that and shows you the real usage within the BI’s shell.
We can go a step further with the ProTop Monitoring and Alerting Service and set a threshold so that we can get alerted once a particular size is reached, significantly ahead of the 90% warning that Progress provides. With adequate advanced warning, we can respond quickly enough to prevent an outage.
If there’s an ongoing pattern, we can also go back in time and find out exactly when the BI spike occurred and when it ended, enabling us to troubleshoot more effectively:
In the above graph, we can see a BI spike starting at 6:40, while eventually disappearing, only to start again at 7:00
Step 2
Monitor and control long-running transactions. ProTop can look for these and alert us when atransaction reaches a certain threshold, so that we can take action before it causes a BI growth issue. ProTop can even be configured to safely auto-terminate a session with a long-running transaction. In the example below, the user has kept a transaction active for almost two hours and needed to be terminated:
Some applications have a transaction timeout feature that logs out the user after a certain period and this can work well. However, it is still a good idea to monitor for long transactions since transactions that initiated outside of the application, such as a batch or progress editor update, may not get caught and cause a BI growth issue.
Step 3
Use the -bithold and -bistall database start-up parameters. This will at least prevent the BI file from growing excessively, but you still need to monitor its growth and prevent it, otherwise a BI stall will occur.
Once you approach 90% usage, Progress will write a warning in the log file. (Gee, thanks for the not so early notice!)
