Bloating backend and other problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike M - 18 Sep 2003 20:13 GMT

Subject sounds funny but we aren't laughing. (Well maybe a little)

We recently switched to a split database setup and are getting
experiencing a considerable reduction in performance. Our Backend
database (Seasonal.BE.MDB) is initially 21. Megs is stored on a win2000
in this manner :
\\SERVERNAME\Employee DataBase\SEASONAL.BE.MDB

We have 6 client machines with local FE files of approximately 2.5 to
3.0 megs names SEASONAL.FE.MDB.
Every hour of use the BE seems to add about 20 megs to it's size. I had
found it at 165.megs this morning I had everyone disconnect and I
compacted the BE and it went back to it's original 21 megs.
We are all using Office XP recently upgraded from access 97 and the DB
converted up from 97 to 2000/2002.
This DB wasn't created as a VB database but do I need to create Access
MDE files for the clients anyways?
We have a considerable wait time when accessing the tables when the BE
has bloated out. How can I prevent this?

Signature

Mike Moore
Computer Services Technician
Snow Summit Mt. Resort
Office (909) 866-5766 Ext.270
mikemoore@ www.snowsummit.com
Contact info not for share, rent or distribution.

Reply to this Message

Albert D. Kallal - 18 Sep 2003 20:34 GMT

The fact of splitting would not case the file to bloat.

Further, you are not clear as to when this problem started. Did it always
experience this kind of growth?

Generally file growth occurs when there is code that uses temp tables. All
data in a temp tables is NOT recovered until you compact. This means that
coding practices should be changed to avoid temp tables when possible.

Here is some reading on this with some additional solutions:

http://www.granite.ab.ca/access/bloatfe.htm

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn

Reply to this Message

Mike M - 18 Sep 2003 20:52 GMT

The bloating is new. The split was implemented as of last Friday (Sept.
12th).
I do not think we added any temp tables. Also it is the backend bloating
not the front end.
Thanks

| The fact of splitting would not case the file to bloat.
|
[quoted text clipped - 14 lines]
| kallal@msn.com
| http://www.attcanada.net/~kallal.msn

Reply to this Message

Albert D. Kallal - 19 Sep 2003 00:19 GMT

>>> I do not think we added any temp tables. Also it is the backend bloating
> not the front end.

Most of the issues and comments in that article equally apply to a be.

The articles in Tony's site also mentions:

ACC2002: To Help Prevent MDB Bloat, Explicitly Close Recordsets - 289562
ACC2002: Running Update Query Causes Database Bloat - 295245
ACC2000: Database Bloats When Importing Large Text File - 239527

All of the above should be checked.

I would also make sure the updates to both office, and *especially* JET are
installed, as they both can fix some bloating problems....

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn

Reply to this Message

Mike M - 19 Sep 2003 17:39 GMT

| >>> I do not think we added any temp tables. Also it is the backend bloating
| > not the front end.
[quoted text clipped - 17 lines]
| kallal@msn.com
| http://www.attcanada.net/~kallal.msn

I ran the Jet update on all machines this morning. Will see how it goes
today. Although it never exceeded 45 megs yesterday and I recompacted to
21 this morning.

Reply to this Message

david epsom dot com dot au - 21 Sep 2003 03:35 GMT

Also, look for and avoid situations where you
append records, depending on a unique index to
reject records. Those 'rejected' records get
added to the table, then deleted: leaving the
'deleted' space taken until the next compact.

BTW, you will find the BE bloating much worse
when the file is shared than when there is a
single user. Who knows why? Who cares... most
of it was fixed in the JET service pack.

(david)

> Subject sounds funny but we aren't laughing. (Well maybe a little)
>
[quoted text clipped - 15 lines]
> We have a considerable wait time when accessing the tables when the BE
> has bloated out. How can I prevent this?

Reply to this Message

Mike M - 22 Sep 2003 16:21 GMT

Thanks David.
Yep I noticed far less problems already after installing the service
pack.

| Also, look for and avoid situations where you
| append records, depending on a unique index to
[quoted text clipped - 28 lines]
| > We have a considerable wait time when accessing the tables when the BE
| > has bloated out. How can I prevent this?

Reply to this Message