Exuberant database file size increase

[John]

Hi

I have a typical front end/back end access app. Additionally a vb.net app
accesses the backend mdb file every few minutes and runs insert and update
queries on some table. The data to be inserted and updated is coming from a
remote db. The mdb file size is normally around 80MB but since running the
vb.net app the mdb file size grows to 1GB or so every few days. After
compact and repair the size does get back to 80MB but it is still a pain to
have to compact repair every few days. What is causing this massive file
size increase and is there any way to control it?

Thanks

Regards

 

['69 Camaro]

Hi, John.

What is causing this massive file size increase

Most likely it's the many updates. If the new data doesn't fit into the space
currently allotted for the record, Jet has to make room for it. If there isn't
enough room in the current data page, then the current data page is split in
two, and the records distributed to each half accordingly. Now there's room in
one of the data pages for this new data in this record, but instead of one 4 KB
data page (for Jet 4.0), you now have two 4 KB data pages. Even if the data
pages aren't full (and even when there are no records in it), each data page is
4 KB. (Exception: Jet 2.0, 3.0, and 3.5 use 2 KB data pages to store data,
because they don't accommodate Unicode characters, like Jet 4.0 does.)

is there any way to control it?

Compact it more often.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Allen Browne]

The action queries are causing the size increase. Access may not release the
previously used space until you compact the database.

The most obvious alternatives are to ensure the INSERT and UPDATE queries
don't make any unnecessary changes. For example, instead of:
UPDATE MyTable SET IsPicked = False;
there would be fewer updates if you used:
UPDATE MyTable SET Field1 = False WHERE IsPicked = True;

Similarly, if you are relying on a unique index to weed out duplicates when
you run your INSERT, use a WHERE clause so there are fewer attempts.

Other alternatives might include using the command line switch for
msaccess.exe to compact the database. Or you might create an mdb file with
the right structure but no data, and have it available as a template. Your
vb.net code could then copy the file, populate it, delete the old one, and
rename the new one so that it takes its place.

 

[John]

The mdb file is used by several users during the day. At night I can try to
compact automatically. Is there any way to find out if no other user apart
from me is using the db is using the db so I can compact it safely?

Thanks

Regards

 

['69 Camaro]

Hi, John.

Is there any way to find out if no other user apart from me is using the db is
using the db so I can compact it safely?

The LDB Viewer will show all of the users currently in the database. You may
find a link to the LDB Viewer and other free diagnostic tools in the "Free
Microsoft Access Troubleshooting Tools" section on this Web page:

http://www.Access.QBuilt.com/html/links.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Lyle Fairfield]

The mdb file is used by several users during the day. At night I can try to
compact automatically. Is there any way to find out if no other user apart
from me is using the db is using the db so I can compact it safely?

I have used the code at

http://www.ffdba.com/downloads/Compact_MDB_of_Linked_Tables.htm

successfully in the somehat distant past.

I believe some may have reported that it did not operate successfully
for them. The function CanBeOpenedExclusively may be helpful in
ascertaining whether or not the compact can be done.

If this were my db I would want to examine the .Net procedures that
added and modified data. The bloat you describe may be related to how
they do their job.