Database doubles in size! Help!!

[meanster99]

I am a novice to db design but I started my db quite a while ago but have only just noticed it keeps doubling in size periodically, even though I have not added many records or changed much. The first time I noticed this phenomena it was at 65Mb (already too big?), it then periodically (and randomly) doubled in size until it was about 480Mb!! I have been told that when db objects are deleted they are not actually deleted and this would explain the size explosions I saw. I have compacted and repaired the db and it now stands at a much more likely 11Mb. However, I really wanted to understand why this occurs so that i could prevent it from reoccuring (I have set my db to compact on close now - which seems to keep it small in size).

I have a whole load more questions aswell but you will see those in the near future!!

thanks in advance.

 

[wazz]

hi meanster.
i had trouble when i was working with images. they should be dealt with a certain way.
i should recommend that you do a search of this site on "bloat", or "size" or words like that. there are other helpful threads that talk about this in detail.
cya soon.

 

[Ashfaque]

There may be various reasons of increasing size of the db

1. PICTURES used in forms or reports db
2. Not compacted & repaired
3. All tables are in the same db
4. Pictures (other than default) used on command buttons
5. Using MDB instead of MDE
6. Removed / deleted some form/(s) but not delted its code that are still
available in db
7. Sometime too much heavy fonts/lables also increases db size.

Try to avoid this all and probably you will find your db is less than enough

HTH

Ashfaque

 

[meanster99]

Thanks Ashfaque - I was guilty of all of these! I guess teaching yourself stuff is not always the bet way!

thanks again.

 

[The_Doc_Man]

Sources of DB Bloat

First, a search of the forum for "bloat" will help.

Second, here are a couple of principles.

a. Every time you open a query, it creates a list of records that match the specified return set for that query. When you close the query, the list is marked for deletion but not actually reclaimed (because it cannot be - see below in this response for why.)

b. Any time you update a record, depending on the nature of the update, you have a reasonable chance that you will not actually UPDATE the record, you will REPLACE it. Most often, I think this occurs when updating a text or memo field and the actual text length changes. For a true binary field, I am not going to swear what Access will do. But for text fields, replacement is a real option.

c. Any time you edit any internal document (such as a form, report, or macro), the old copy is marked for delete but not deleted.

d. Any time you edit a module, each line you change is marked for delete but not deleted.

e. Any time you run an import/export wizard, there is a significant chance that the workspace you used is marked but not deleted.

Why? Because Access knows that it is a SHARED system, so it cannot do real-time compaction. Why not? Because the pointers that would tell it where the temporary structures were written don't exist in the disk copy of the DB, they exist in your WORKSPACE - which is local to your machine. If your DB really IS being shared, there is no way to know what elements are good or not. Access just allocates space and hopes you will remember to clean it up later.

The way a COMPACTION operation works is, it creates a new, empty DB - then finds each non-deleted structure and copies it to the new DB file. Finally, it renames the old DB, then renames the new DB. There is no other way to get rid of deleted DB objects in Access but to create a blank DB and "start over again" with only the non-deleted objects.

 

[moviestar]

More information on Compaction

Hi Doc Man

Can you give me more information about the last paragraph in your recent post about Compaction - the Access 2000 database I've developed at work reached 27.3MB and I got an error message this afternoon saying "Record too large" when trying to update information using one of the forms. I looked on this forum for guidance, then compacted / repaired the database which reduced it to 16.2MB and produced a new database (named db1) which was over 40MB. I assume that the new db1 database can be deleted and that I should carry on using the existing named database which is now smaller. Just wanted to check with you as you talked about renaming old DB, then renaming new DB - what names does it give each of these so you know which is the good one to carry on using.
Thanks in advance!