Database back-end getting 'bloated' over time? (1 Viewer)

[dlambert]

I have been having a problem with by Access back-end part of my company database, maybe someone has an idea of what is causing this and how to resolve it?

Here is an explanation/example of the problem:

- Back-end crashes, front end cannot access it anymore.
- i look at my back-end file (ETDB_be.accdb) it has a size of 29,484kb.
- I already have a system in place to do regular backups of the back-end file to Excel and that automatically generated file of all the tables and data has a size of 9,876kb
- I re-start the computer holding the back-end file (only way i know to force all open connections to close)
- I make a copy backup of the back-end file just in case
- i the open the back-end file in Access and perform a "Compact and Repair Database" function
- After that i close the database and everything starts working again (front end connects and everything works no problem)
- But i notice that now the back-end file (ETDB_be.accdb) now has a size of 5,556kb - and if i do a backup to Excel it has a size of 9,876kb exactly like before.

So it looks like the access back-end file is somehow getting 'bloated' over time, reaching a point where it stops working, and doing a "Compact and Repair Database" function reduces its size back to what it should be (without changing the actual amount of data in it) and makes it work again (thankfully that works perfectly to solve the problem)
This happens to be roughly every 2 months.

Any ideas if there is a way to avoid this regular problem? or is there a way to automatically do a "Compact and Repair Database" every week or so?

as always, may thanks for any advice

 

[GPGeorge]

I have been having a problem with by Access back-end part of my company database, maybe someone has an idea of what is causing this and how to resolve it?

Here is an explanation/example of the problem:

- Back-end crashes, front end cannot access it anymore.
- i look at my back-end file (ETDB_be.accdb) it has a size of 29,484kb.
- I already have a system in place to do regular backups of the back-end file to Excel and that automatically generated file of all the tables and data has a size of 9,876kb
- I re-start the computer holding the back-end file (only way i know to force all open connections to close)
- I make a copy backup of the back-end file just in case
- i the open the back-end file in Access and perform a "Compact and Repair Database" function
- After that i close the database and everything starts working again (front end connects and everything works no problem)
- But i notice that now the back-end file (ETDB_be.accdb) now has a size of 5,556kb - and if i do a backup to Excel it has a size of 9,876kb exactly like before.

So it looks like the access back-end file is somehow getting 'bloated' over time, reaching a point where it stops working, and doing a "Compact and Repair Database" function reduces its size back to what it should be (without changing the actual amount of data in it) and makes it work again (thankfully that works perfectly to solve the problem)
This happens to be roughly every 2 months.

Any ideas if there is a way to avoid this regular problem? or is there a way to automatically do a "Compact and Repair Database" every week or so?

as always, may thanks for any advice

I seriously doubt the back end is crashing because of "bloat".

29,484 KB translates to roughly 29MB. Access accdbs can contain up to 2 GB, or 2,028 MB. You're well under that limit, assuming the reported file size is accurate. Actually, 29MB seems minimal, and 5.5MB is really quite small as Access accdbs go.

I'm curious if a) the reported sizes are accurate, and b) what ELSE might be happening that would account for the crashes.

 

[The_Doc_Man]

1. A bloat level of 29.5Mb is nowhere near a show-stopper level of bloat. When we start talking over 1 GB... there is when you have a problem.

2. Bloat is a consequence of a busy database for which the engine allows BEGIN/COMMIT and ROLLBACK operations - even if you aren't doing such things very often. Using the CurrentDB.Execute SQL-string sequence to update a table also allows for rollbacks. The mechanism is that Access prepares the update as a parallel set of records, then at the moment of the commit, unthreads the old (pre-update) records and threads in the new (post-update) records. During that period, BOTH sets of records exist because you might have to do a rollback, but only one set of records (at a time) is part of the table. At the end of the non-rollback sequence, the old records are now out of the table... except that it is possible that they were being read by someone else. So the mechanism of Access leaves the old records around. If you DID a rollback, it is the NEW records that get left hanging around.

There is also an issue with "garbage collection" in a shared, dynamic-memory environment that has plagued computers for over 40 years. (No, not exaggerating in the slightest about the age of that problem.) So the end of this story is that the "bloat" you describe is the natural side-effect of updating any tables. Which is why in a good, stable environment, you STILL need to schedule some time to not only back up your database, but also to compact it.

3. "Stops working" is NOT caused by the bloat. (But the bloat may be caused by a "stops working" event.) Cause and effect point to bloat as "effect" in this situation. You need to determine the "stops working" as not a gradual event, but rather something of an immediate nature.

EDIT: The mechanism in #2 above is ALSO the reason that tables have no inherent order when accessed via non-sorting recordsets or queries.

 

[CJ_London]

another possibility for bloat is you are creating temporary tables and then deleting them

- Back-end crashes, front end cannot access it anymore.

what is the error message the user gets? It may be the result of a recent update

Error in Access when opening a database on a network file share - Microsoft Support

support.microsoft.com

Solved - Unable to access backend after Windows Update

Hi All I have a shared folder with the backend of a split database and users access it via a front end local to their machines. If no one has their front end open i can open the front end on my local machine accessing the backend via the shared folder on my local machine. If someone else...

www.access-programmers.co.uk

 

[dlambert]

yes, i agree that of course 29MB would not be a problem in itself size-wize. i just found it interesting that every time that back-end stops working, it can be fixed by doing a compact and repair which results in a significant reduction in size of the file (around 80% reduction)
i guess i need to either find an automatic way to regularly do a compact and repair operation, or i am just being lazy and i can just manually do it once a month which is really not that much of an effort (just have to set a reminder for myself)

 

[The_Doc_Man]

We have seen issues with automated compact/repair operations. The SAFEST (not nearly the most convenient) method is:

1. Set aside a maintenance time. Cast that time in concrete and shine spotlights on it. Get one of those kinky sex-toy whips and hang it up with a label "reserved for use on people who don't honor the maintenance time." Fire anyone for whom that whip would be an enticement.

2. At the maintenance time, assure that everyone is out of the DB. (If not, get the whip...)

3. Do a file move of the shared file to a work area. Doing this renders all front-ends useless since their file binding includes a path. This takes the file out of that path. Use the Windows Explorer to perform the copy as a monolithic file move. (DO NOT, under ANY circumstances, attempt to have the file move itself.) RENAME the original file in the work area so that there is no way for someone to open it with the FE file.

4. Make a COPY of the file to its proper name. Do a COMPACT & REPAIR on this file. (You still have the original under a pseudonym for safety).

5. If all goes well, make a backup copy of the compacted, repaired file. Include a date-stamp in the name or a sequence number or something. If the C&R fails, discard this copy. Make another copy and try again. If this step fails repeatedly, you have a serious corruption and might have to revert to a backup copy for production purposes.

6. Now move the C&R'd, backed-up file from the work area to the shared directory. Let your users back in. You can now dispose of the file you moved in step 3 above. That file is now history (figuratively and literally).

As to your "stops working" problem, you need to know WHY it stops working. However, "bloat" is a symptom, not a cause. You need to find out the error message for the person/persons who suddenly lose functionality. You also want to know if anyone had a computer glitch, crash, power drop, network drop, or other physical event immediately prior to the "stops working" problem.

 

[CJ_London]

another factor can be users connecting wirelessly to the BE

 

[bastanu]

Please feel free to have a look at my free utility that uses the approach described by the_Doc_Man; you can schedule it to run at night using the built in Windows (task) scheduler, you have to ensure that the computer you are running it on still has access to the network resources (the location of the original shared back-end). This usually involves having a user remaining logged on.

FBA MS Access back-end compacter - ForestByteApps

ForestByteApps - A place for Microsoft Access code samples and utilities to make your life easier. Free Microsoft Access tools and sample VBA code

forestbyte.com

Cheers,

 

[GPGeorge]

Also, don't forget there are two parts to "Compact and Repair Database"

 

[AngelSpeaks]

We have seen issues with automated compact/repair operations. The SAFEST (not nearly the most convenient) method is:

1. Set aside a maintenance time. Cast that time in concrete and shine spotlights on it. Get one of those kinky sex-toy whips and hang it up with a label "reserved for use on people who don't honor the maintenance time." Fire anyone for whom that whip would be an enticement.

2. At the maintenance time, assure that everyone is out of the DB. (If not, get the whip...)

3. Do a file move of the shared file to a work area. Doing this renders all front-ends useless since their file binding includes a path. This takes the file out of that path. Use the Windows Explorer to perform the copy as a monolithic file move. (DO NOT, under ANY circumstances, attempt to have the file move itself.) RENAME the original file in the work area so that there is no way for someone to open it with the FE file.

4. Make a COPY of the file to its proper name. Do a COMPACT & REPAIR on this file. (You still have the original under a pseudonym for safety).

5. If all goes well, make a backup copy of the compacted, repaired file. Include a date-stamp in the name or a sequence number or something. If the C&R fails, discard this copy. Make another copy and try again. If this step fails repeatedly, you have a serious corruption and might have to revert to a backup copy for production purposes.

6. Now move the C&R'd, backed-up file from the work area to the shared directory. Let your users back in. You can now dispose of the file you moved in step 3 above. That file is now history (figuratively and literally).

As to your "stops working" problem, you need to know WHY it stops working. However, "bloat" is a symptom, not a cause. You need to find out the error message for the person/persons who suddenly lose functionality. You also want to know if anyone had a computer glitch, crash, power drop, network drop, or other physical event immediately prior to the "stops working" problem.

Hey Doc, so the database option to compact when closing should NOT be checked?

 

[The_Doc_Man]

The problem with checking that option is that the one you can check applies to the front end. There IS such a thing as auto-compacting the back end, but you have to directly open it for that to occur. Auto-compact/repair does not apply to linked back-ends, only to things opened directly (or at least that is how I remember it.)

 

[AngelSpeaks]

My FE has the bloat issue because of a temporary table. The BE is fine.

 

[The_Doc_Man]

My FE has the bloat issue because of a temporary table. The BE is fine.

This is a case where you solve the problem by having a script to launch the FE and it just copies a new version of the FE to the place from which you would run it. As long as the FE doesn't have any long-term local tables, the "replace the FE" script is perfect as a way to limit FE bloat.

 

[AngelSpeaks]

Thanks Doc.

 

[bastanu]

@cathy: I think that if the front-end is used from a local drive (as it should) it would be OK to have the Compact On Close option checked. Usually the problems with that option are when it is used on a back-end type file located on the network and you encounter a network failure\interrupt during the compacting.

Another option would be to simply move the temporary table to another Access file (which can be located anywhere), link it back into your front-end and schedule a backup\compact for the new file using one of the available utilities.
Cheers,

 

[AngelSpeaks]

@cathy: I think that if the front-end is used from a local drive (as it should) it would be OK to have the Compact On Close option checked. Usually the problems with that option are when it is used on a back-end type file located on the network and you encounter a network failure\interrupt during the compacting.

Another option would be to simply move the temporary table to another Access file (which can be located anywhere), link it back into your front-end and schedule a backup\compact for the new file using one of the available utilities.
Cheers,

Moving the table may be an option. Client's copy of Access is on Citrix server so to run the FE, he has to launch Access and then open the database. Because of the bloat, when I try to made an ACCDE, i get a message that the database is too big.

 

[Cronk]

I have several applications running where there are temporary data tables but in a separate db in the same folder as the FE. I don't worry about compacting the temporary db. It's easier to blow it away and every so often create a new temporary db with empty tables .

 

[AngelSpeaks]

I have several applications running where there are temporary data tables but in a separate db in the same folder as the FE. I don't worry about compacting the temporary db. It's easier to blow it away and every so often create a new temporary db with empty tables .

Thanks Cronk

 

[Isaac]

Simply put, regularly (not necessarily extremely often, just with regularity) doing C&R of the back end is a normal required component of Access development.

It would be totally normal to see the C&R'ed version go down significantly in size, while not changing the # of records "currently" (visibly, outwardly) in the database - which is what you're putting to Excel, so that all makes sense.

In heavily used Access back ends (back when I used Access back ends), I would C&R them about monthly, if I could successfully get everyone to cooperate (which is easily solved by announcing your Down Time with advance notice, and incorporating a FE component that forcibly kicks people out if needed as a break-glass measure).

 

[The_Doc_Man]

Yep, Isaac, I had to have a mechanism that would self-eject. I had a timer on my dispatcher form (that never closed, just hid sometimes). It would check the scheduling table that knew the next Downtime - which was also published on the dispatcher form saying "NEXT DOWNTIME IS xxxx" and it changed colors the closer we got. If we got to the scheduled down time, all of my forms had a thing to check the status of that flag every time you did something. They also had consistent control names so that the "kick out" code would be able to "click" the "UNDO" button followed by the "CLOSE" button. Wasn't easy. Didn't always work. But it worked at least most of the time.