Compact and Repair -- Why? How? and How Often?
Dec 25 2007, 09:19 PM
From: Cincinnati, Ohio, USA . . . ><((((°>
A: Why should I compact and repair my database? It works fine!
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)
Q: What should I do before compacting my DB?
A: 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. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif) )
Q: How do I C/R my 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...
Click the Microsoft Office Button> Manage> Manage this database> Compact and Repair Database...
Q: "How often should I C/R my database?"
A. 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.
Q: "Should I use the Compact On Close option?"
A: 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.
Q: "My database is split (FrontEnd on local machine/BackEnd on network). How do I handle this scenario?"
A: In a FE/BE 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.
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.
I would like to thank the many members of the UA community who contributed to this FAQ.
|Go to Top · Lo-Fi Version||Time is now: 26th May 2013 - 02:11 AM|