Before Image Growth

[jennid]

Hello All-We have been experiencing rapid growth of the before image files. About a year ago we implemented an application that connects to the Progress db via odbc. Since that time, it seems like we've had a lot more before image growth than we did in the past and over the past couple of weeks, it's gotten so bad that we're doing emergency shutdowns of the db once or twice a week. Prior to the odbc app, we would experience this once or twice a year and were always able to identify the user or poorly scoped program causing the issue by looking at active transactions in Promon. However, whenever we experience bi growth now, we see NO active transactions in Promon. If the odbc app is causing the growth, would we see active transactions? Since we're not seeing any active transactions, how can I get to the bottom of what is causing this? Any suggestsions would be appreciated.

Thanks,

Jenni Detert
Manager of Application Development - Endries International, A Ferguson Subsidiary

 

[TomBascom]

What have you set the isolation level to?

Oh, and what version of Progress are you using?

 

[jennid]

We're on verson 10.0b05 (we'll be upgrading sometime in the next 6-12 months).

Where do I check the isolation level?

Thanks.

 

[tamhas]

Where you set up the ODBC connection.

 

[jennid]

There are two odbc connections that the application is using. One has an isolation level of Read Uncommitted and another has Repeatable Read. One is used when writes need to be made to the Progress db and the other is used when only reads are needed.

 

[TomBascom]

I suspect that your update connections are the problem.

There was a very nice presentation given at "Virtual Exchange" called "Thinking Like SQL" or some such which covered the sorts of things that you need to do to get SQL connections to behave. You might want to take a look at that.

 

[jennid]

Where can I view the virtual exchange presentations? I'm assuming they are somewhere on Progress's website, but I'm finding them.

 

[tamhas]

http://events.unisfair.com/index.jsp?eid=436&seid=105

The link is in the news box at the left on the main page.

 

[doom1701]

I should take a look at the presentation to see if there might be mention of our issues. I've found that, even with an ODBC connection set to Read Uncommitted on 10.1b, if the connection stays open, it keeps a transaction open (it shouldn't even be using a transaction). I've got an alert set to notify me if our variable BI extent gets touched, so I can go in and disconnect the SQL user that has an open connection.

I've got some code that someone here gave me to look for open transactions; with that and a separate DB user for each major ODBC system, I can track down which one is keeping it's connection open.

We're going to 10.1c or possibly 10.2 something in the near future, and I'm hoping that this goes away...