Compact and Repair Your Database

	Microsoft Access
	This came up again, so I thought I would share it with you. As an Access database is used and records are both added and deleted, the datbase grows. BUT, the allocated space (on disk) for any deleted records is not actually removed. For example: You have a table with 5000 records and you go through and clean up any outdated or obsolete records. This seems like it should help make the database as a whole perform faster and more efficiently. However, if you look at the total size on disk before you delete the records and compare it with the size afterwards, you will see that the size has not changed at all. I suppose that MS Access is trying to protect you and itself by not removing that space immediately.?! So, what is a DB Administrator to do? A process called Compact and Repair Database is provided to take care of this issue. The Compact and Repair Database utility actually does two things: It removes the space taken up by previously deleted records. It reinitializes the keys and indexes associated with this database. You see MS Access is also reserving the space within the keys and indexes of those same deleted records. The other time you might use this utility is when the database is acting really odd and giving strange error messages. (Sorry I don't have any examples close at hand.) So how do we start this Compact and Repair utility, Stuart? (Hang on and I will show you.) First of all: ** BACKUP YOUR DATABASE BEFORE CONTINUING !!!! ** From MS Access 2003, we use the menu bar and go to Tools>Database Utilities> Compact and Repair Database. (see figure 1)
	Figure 1: Compact and Repair from Access 2003
	From MS Access 2007, we start at the Office Button, then choose Manage > Compact and Repair Database. (See Figure 2)
	Figure 2: Compact and Repair from Access 2007
	Depending on the databse size, PC/server speed, etc. this process can take from 90 seconds to a very long time. The PC doing the job doesn't really look like anything is going on (no hourglass), except that you can't access any regular functions until the compact and repair process has completed. As I said at the beginning, I just worked with a client on this very topic. They had a MS 2003 Database that was approaching the (stated) limit of 2 GB. After the Compact and repair utility had finished, the database shrunk to 35 MB. The performance jumped significantly. So give it a try and see if you see any difference.