Why is my BI variable extent growing?

[doom1701]

A few months back, our DB shut down because the variable BI extent hit 2GB. Since then, I've monitored the BI variable extent much closer, and I have our alerting system notify me if we grow into it. Our setup right now is 2 128MB BI files, the 2GB one that I had to convert to fixed (need to fix that sometime) and the variable.

When we do our offline backup each weekend it truncates BI and resets the variable extent, but for the past two weeks, we've started growing into it early in the week (Monday or Tuesday). So far, we've never approached 2GB, but the growth still concerns me. Once the transaction is finished, doesn't the BI space become "available" again?

What can I do to try to find out what is causing this? The issue seems fairly new, but we haven't implemented any new code in months. We could be using functionality in the system (SXE/Trend) that we hadn't used it the past, and it could have some pitiful transaction handling--but I don't know where to begin to look to find it. Even if I can track down the users that have these transactions I could probably find out what's going on.

 

[taqvia]

As a first debugging step write a small script by using VST to figure out all transactions that are being opened for some time say 15 or 30 min.

for each _trans no-lock where
_trans-usr ne ?
and _trans-duration >=(time you want to specify):
for first _connect where _connect-usr = _trans-usr no-lock:

disp _connect-name _connect-device _connect-pid _trans-duration.
pause.
end.
end.

Write a script based on the above and schedule it to run through cron to figure out which all transactions are holding for a long time and then also check the code involved.

Meanwhile you can also check the duration of bi checkpoints.

Arshad