Back-end Bloating

[Guest]

I have a A2000 backend database with appx. 30 users. The front-end size
appear to be stable, but the back-end bloats an additional 2000MB to 8000MB
each day. When the overall size get to above 70MB the database crashes. What
can be causing this?

Any help would be appreciated. Thanks.

 

[Jeff Boyce]

I'm guessing you mean it bloats by 2 MB to 8 MB, as the limit for an Access
..mdb is only 2000 MB (i.e., 2 GB).

If your database is storing images (and I believe other OLE objects), it can
bloat like this.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thank you for your response Jeff. The db is not storing any images, just
text. Any other ideas?

 

[Jeff Boyce]

Text as in "A string of characters", or text as in "A Word document"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

--
198fmicrosoft

maybe the download space is 666MB

thanks

 

[Guest]

Hello Jeff,

Text as in "string characters". i.e. name, address, ssn, etc.

Thanks

 

[Guest]

Are you generating some temporary tables?

 

[Tony Toews [MVP]]

I have a A2000 backend database with appx. 30 users. The front-end size
appear to be stable, but the back-end bloats an additional 2000MB to 8000MB
each day.

I find that after compacting the BE bloats by a few Mb the first day.
This depends on the size of the database. One clients 300 Mb database
would bloat by about 20 Mb by noon the first day. But after that it
only got bigger by 500 kb or so per day.

When the overall size get to above 70MB the database crashes. What
can be causing this?

What do you mean by crash? Do you have a specific error message?

The crashing probably doesn't have much to do with the bloating.

For more information on corruption including possible causes,
determining the offending PC, retrieving your data, links, official MS
KB articles and a list of vendors who state they can fix corruption
see the Microsoft Access Corruption FAQ at
http://www.granite.ab.ca/access/corruptmdbs.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Guest]

What I've found is that as you "do things", there is "white space" created in
the database. Every time a query is run, every time a report is generated,
and (of course) when new records are added. This "white space" (I think of it
as workspace that Access creates to do whatever tasks you requested) never
goes away. In order to get it to go away, you need to do a periodic "compact
and repair", which removes all that extra dead-space.

Smart money would be on converting the backend to a SQL database, and just
keeping the Acces frontend, with all the simultaneous users you have. Access
is NOT optimal for large multi-user databases.

JMHO

 

[Albert D. Kallal]

A few other things to check:


in reocrdset code...always close,

rstData.Close
setrstData = nothing ' this is optional..but, a good idea

And, don't use docmd to run sql updates, they are wrapped in a transaction,
and thus cause bloat

so use

currentdb.execute "you sql"

avoid:

docmd.runSql

Make sure you installed the updates to JET, and office..both fix some bloat
problems....

 

[Tony Toews [MVP]]

Smart money would be on converting the backend to a SQL database, and just
keeping the Acces frontend, with all the simultaneous users you have. Access
is NOT optimal for large multi-user databases.

30 users is quite reasonable as is 70 mb BE. There are other factors
I'd consider before number of users in this situation such as can lost
data be re-entered.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

in reocrdset code...always close,

rstData.Close
setrstData = nothing ' this is optional..but, a good idea

Why do you say that it's optional? Closing the recordset releases
the memory, but setting the variable to nothing clears the pointer.
In some cases, a non-cleared pointer will cause the memory to not be
released (the whole "reference counting" problem which is the reason
variables going out of scope doesn't always release memory in the
first place).

Doing both isn't voodoo.

It's insuring that all the memory structures and pointers you've
used are cleared.