Access db size

[Yossi evenzur]

it seems like even though the db is relevtively small, suddenly the db size
increases by facotr of 10, e.g. 5MB db becomes 50MB with no apparent reason.
any idea why?

 

[Allan Murphy]

Are importing Spreadsheets or doing a lot of calculations?

Allan

 

[Yossi evenzur]

yes, 1-8 MB, note that at the beginning, e.g. imidiatly after the import the
size was ~ 30 MB, now it's over 60 MB.

 

[mcnews]

yes, 1-8 MB, note that at the beginning, e.g. imidiatly after the import the
size was ~ 30 MB, now it's over 60 MB.

compact & repair

 

[Yossi evenzur]

can you explain a bit more?

compact & repair

 

[Clif McIrvin]

Search this newsgroup archives (Google groups works well) for db size,
or db bloat ... there have been quite comprehensive discussions of this
subject recently.

 

[Stockwell43]

Open your database and go to Tools - Database Utilities - then Compact and
repair.

Remember the more information you add and/or import, you are using space in
the database. It's good to compact it every now and then.

If you do a lot of this importing, you may want to set the database up so
it compacts on close. Go to Tools - Options - click on the General Tab -
click the checkbox the says "Compact on Close"

 

[Fred]

Also make sure you aren't using OLE fields to store pictures.

 

[Tony Toews [MVP]]

Also make sure you aren't using OLE fields to store pictures.

A2007 does a much better job in that area than A2003 and previous.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Larry Linson]

can you explain a bit more?

Tools | Database Utilities | Compact & Repair

Check Access Help on Compact & Repair

Access is unlike many software packages -- it does not return unused blocks
of storage to the system after it is done with them _until_ you perform the
Compact & Repair.

Larry Linson
Microsoft Office Access MVP

 

[Tony Toews [MVP]]

Access is unlike many software packages -- it does not return unused blocks
of storage to the system after it is done with them _until_ you perform the
Compact & Repair.

I thought most database systems worked that way though.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Larry Linson]

I thought most database systems worked that way though.

"Most database systems" is a "tall order" for me to know about. Some
database systems perform something similar to compact and repair "in the
background" as they perform other work, either default or optionally, so you
don't have to take explicit action (I've been told that option can be
selected on recent versions of MS SQL Server, and believe that was the case
back in the 1990s for Informix, as well). Server databases, especially
those for applications deemed "mission-critical", have to be available
(essentially) 24/7, and doing this in the background allows them to be
available 24/7.

Some long years ago, I wrote the "Data Base Handler" in IBM 360 Assembler
Language which, er, "handled the database" for a real-time system for
performing transactions. It certainly was a far cry from a "full-featured"
database, but it looked up necessary information, recorded actions, and made
a record of transactions for the remote systems for which it was acting as a
proxy. It returned each block of memory (RAM and disk records) to the
system for re-use, immediately, as soon as the information in the block was
deleted.

I worked on other mainframe application systems in which, at an appropriate
time in a daily schedule, the system was made unavailable so "file
maintenance" could be performed -- one of these, interestingly, used
mainframe ISAM, so it was, indeed, very much like "compact". In that
environment, it was termed "reorganizing the files".

Larry Linson
Microsoft Office Access MVP

 

[David W. Fenton]

I thought most database systems worked that way though.

Certainly, every database engine I've ever worked with did (dBase,
Paradox, Access, SQL Server, MySQL, to name the ones that I recall
right away).

 

[David W. Fenton]

Some
database systems perform something similar to compact and repair
"in the background" as they perform other work, either default or
optionally, so you don't have to take explicit action (I've been
told that option can be selected on recent versions of MS SQL
Server, and believe that was the case back in the 1990s for
Informix, as well).

But running an agent to compact the data is just running an agent to
do compacting that wouldn't otherwise happen.

It seems to me that any database engine that rewrote the data every
time it was changed in order to recover unused space would be so
incredibly slow as to be completely useless. And that, of course, is
why I can't think of a single one that I've ever encountered that
does in fact re-use unused space without an out-of-process
operation.