Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

We have an Access 2000 format database running in Office Access 2003.

We have a problem with the size when I merge the database; the size is about 250 MB. Two days later, the size has ballooned to about 750 MB.

How can I see where this increase in size comes from? Can I look behind the scenes in Access?

The database is running for a tennis club and our employees are working all day long with the database. Another programmer made the database and says it is common to see Access databases increase in size.

What can I do to alleviate this issue?

share|improve this question
As I recall, the Jet engine (or whatever runs MSAccess in 2000 format) commonly uses the MDB file to store its scratch space, so it doesn't have issues with temptables. This is most likely what's happening. – jcolebrand♦ Jul 30 '12 at 19:36
There are things that can help, for example, make sure you do not create and drop tables in the usual running of the database. – Remou Jul 30 '12 at 20:54

2 Answers

up vote 3 down vote

MS Access grows just from using it - running queries, etc. That is just the nature of it.

I would recommend including a compact and repair process in your daily processing. Access removes the existing file and replaces it with the compacted version, thus shrinking the database.

If you are worried about the size, the consider creating a daily process (nightly) to run the compact and repair. I had an MS Access database that we had to run a nightly compact/repair to shrink it from ~2GB to 300MB every night.

share|improve this answer
Thanks for the answers. But we have an extra problem with the automatic routine of shrinking and compacting. We use the database in a network environment where the MDB file is on the server and a several different clients are using the database during the day. I think the compact/repair function needs exclusive access to the database file. So how can I implement this? – Joël Craenhals Jul 31 '12 at 4:38
@JoëlCraenhals we had the same thing a DB being used across multiple locations. What we did was implement a timeout/logout feature to trigger around midnight. If/when the users would be logged off, we would have a process to execute via VBA and a form with a timer event to compact/repair the database – bluefeet Jul 31 '12 at 14:05
So the logout function closes the database on all the computers except one? And on that one computer the database was compacted? – Joël Craenhals Aug 1 '12 at 6:44
@JoëlCraenhals we had a split database system in 3 pieces. Import DB, Backend DB and front-End. The Backend was the DB that would need to get compacted and we would logout everyone from that system. The Import DB ran on a separate machine and would open the Backend DB and compact it. – bluefeet Aug 1 '12 at 10:40
up vote 3 down vote

All you have to do is the following:

  • Across the Top of the Access Window, Click the File Tab
  • Click the Compact and Repair Button

I would not worry about growth because data insertion into MDB usually appends changes to back of the file. Garbage collection and space reusage is not a strong suit for MSAccess. To make matters worse, there is filesize limit (I think it is 2GB) on MDB files. This is why some migrate to SQL Server Express where SQL Server can be configured to perform automatic Compacting and Repairing.

share|improve this answer
4  
;-) Now tell him why. :p – jcolebrand♦ Jul 30 '12 at 19:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.