UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Compact and Repair    
Compact and Repair

Related Content:
    Compact and Repair Database over a Network


Contents

Reasons for C/R'ing

"It works fine!", you may say. That may be true, but could it work better? Compacting and repairing (hereafter referred to as C/R) of a database can be likened to changing the oil in your car. You may be able to drive your car without changing the oil, but over time, performance will degrade and eventually you will have a major problem show it's head. The same is true with a database.

As a database performs its day-to-day internal tasks, eventually some "dead weight" starts to develop. At first it's barely noticeable, but eventually it can cause slowness and an increase in size of the overall application. There's nothing intrinsically wrong, … it's just how DBs work. (Especially file-based DBs, like Access)


Before C/R'ing Preparations

Make a backup. I put this first because, well, because it just makes sense. Any time you give complete control over to a process that can turn your database inside out, it just makes sense to have a backup! (Plus you can never have too many backups. )


How to C/R a Database

You must have Open/Run and Open Exclusive permissions for an Access database in order to compact and repair it.

For versions ACC97-2003: Click: Tools> Database Utilities> Compact & Repair Database...

For ACC2007: Click the Microsoft Office Button> Manage> Manage this database> Compact and Repair Database...


Interval Between C/Rs

This is somewhat dependant on what your database "does" and what you have recently "done" to it.

As a "best practice", it is a good idea to perform a C/R:

  • after any design changes have been made
  • after a large number of records have been affected in some way (APPEND, UPDATE, DELETE)
  • if your database is reaching its maximum size (1GB for pre-ACC2000, 2GB for ACC2000+) *

* If your database is consistently reaching its maximum size, it may be time to consider moving to a larger database container (i.e. SQL Server, MySQL, etc.) or splitting your database into multiple .MDB files and linking them together.

Not to set any hard and fast rules, but keep an eye on the file size before and after a Compact process. Don't worry too much about a few kilobytes here or there, but if the compaction reduces the file size by a large amount, you may want to compact a little more often.


Compact On Close

There are some who have taken the convenient step of selecting Compact On Close, an option available to you. But one must exercise caution when using this option. The actual internal processes that happen when a C/R is performed are quite complex (I'll not bore you with the details). Anytime these major changes occur there is a (small) risk that your DB can corrupt. On rare occasions (and I do mean rare) you may not even notice the corruption until it gets compounded by future C/Rs. By that time, your backups may even be too outdated to roll back to a good copy. So while Compact On Close is very convenient, it may not be the best practice to get into.


Split FE/BE Client/Server Scenario

In a Front End/Back End (FE/BE, or Client/Server) scenario, it is seldom necessary to compact the distributed FE, as no design changes are made to it. Unless you are storing data in local tables and constantly modifying them, there should be almost no increase in size ever to the FE. Therefore there is limited to no value in C/R'ing a FE regularly.

What does need to be C/R'd is the BE (where the majority of data is stored). C/R should never be performed across the network.** It should be performed on the machine that holds the DB. So you have a few options:

  1. Sign on to the server (or machine that is sharing the BE)
  2. Ensure that no one is actively using the database ***
  3. Perform the C/R.

Or, if you do not have access to the server:

  1. Ensure that no one is actively using the database ***
  2. Copy the database to your local machine
  3. Perform the C/R
  4. Copy the database back to the server

** A word of caution here. In rare cases, some have reported major corruption from C/R'ing across the network. Others have reported absolutely no issues in compacting across the network and feel comfortable doing so. The actual cause of the corruption is unknown, but may be attributed to network instability or even non-Microsoft networks. In either case, if you choose this route, make sure you have backed up the database before starting the C/R process.

*** Usually this can be detected by the presence of an .LDB file. There are also utilities in the UA Archives to facilitate the detection of user activity and also a means to notify them that database maintenance needs to be performed.

Also note that using the Compact On Close option on a BE does not compact it during normal usage. It is only when it is directly opened that the Compact On Close would initialize.


Conclusion

In summary, there really isn't a specific time to perform a C/R, but a regular schedule is good thing to set up, whether it be once a day, once a week, or once a month. The UA Code Archive is a good place to look for a way to automate this process.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 20,269 times.  This page was last modified 16:52, 11 February 2012 by Jack Leach. Contributions by Walter Niesz  Disclaimers