DB Memory Spikes

[JJman]

Hi...
I have to compact our medium sized Acess 2K production database every morning because, even over the course of a single day, it can increase in size significantly. It's not uncommon to compact it from 80 or 90 megs down to 65 or 70. I've always wondered how it could increase in size so much even though I know that nowhere near that much info is going into it each day. I understand that the file becomes fragmented over time, but the size of these spikes seems excessive. Just since yesterday morning, it went from 70 megs (after compact) to a staggering 123 megs today. I compacted it as usual and dropped those extra 53 megs. Can anyone tell me what might be causing these kinds of memory spikes? Thanks!

 

[vbainchicago]

Did you build this DB, or is it an inherited DB?

VBAinChicago

http://www.jjscomputing.com

 

[MichaelRed]

Generally speaking, Ms.A. creates objects, but seldom actually destroys them. Ocreated in an "Ad-Hoc" manner and deleted even within a session will continue to actually remain in the ".MDB" even when closed. Thus, if you have 'programmed" (for example) a tenmoporary table (or report) into the app even though it is deleted it will cause "bloat" of the application. Likewise, even a "temp" table which is "emptied" and reloaded with new data will not relinquish the space required by the 'deleted' records (until the Compact is done).

In many instances, applications are not set up properly and generate even more bloat. This occurs often when an Ms.A. App is deployed on a central (server) processor as an "integrated" application, where each user accesses the entire app through the server. In this situation, each user creating (and / or) deleting temporary records (or other objects) cause the space on the 'integreated' application to increase, but it is not "decreased" until some one (you?) do the compact.

To see more on the overall subject, use the ubiquitous {F1}[ (a.k.a. HELP) on the subject of split database, Front End, Back End, and other terms.

Also use advanced search on these and similar terms within these fora.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[psemianonymous]

What MichaelRed said is dead on (as always). I'd just like to add that size bloat does not affect performance of the database as much as you would think. So if it gets to 120MB after one day from 80MB, maybe that's unusual, but from 90->70 is not unusual at all, nor is it a cause for concern.


Pete

...but it definitely wouldn't hurt to split the application, nor eliminate a process that uses temp tables on a shared network MDB...

 

[JJman]

Helpful info, thanks everyone. We will soon be moving the back end into SQL 2000, and I assume (or hope) that that will eliminate the issue.

 

[vbainchicago]

In the meantime you could use a batch file or something similiar to run the compact procedure for your DB automatically. I have some DB bloat, but every night at 11:00 a handly little batch file opens, compacts, and closes the DB so it's ready to roll when the users arrive in the morning.

Good luck on the SQL backend project!

VBAinChicago

http://www.jjscomputing.com

Just for fun -

http://www.321google.com

 

[MichaelRed]

" ... back end into SQL 2000, and I assume (or hope) that that will eliminate the issue ... "

Depends on how careful you are in the 'move'. Objects (including records) created within Ms. A. will STILL contribute to bloat. You probably will not totally eliminate the growth unless you (severly) restrict the capabilities of your users with security and have NO temporary objects created within Ms. A.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places