Should I compact DB?

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I have learned recently (in this forum) about compacting
and repairing databases using the built-in utility. After
copying some databases, I have run the utility, which has
decreased the size of the databases, and maybe they run
faster now. All seems to be well with the compacted
databases. Is compacting a good idea? How often? Should
I set it up to run automatically each time the DB is
closed? If there is another user on the DB, will this
produce an enigmatic error message? Are there risks to
compacting? Anything else I should know about this?
 
You always have 'if the application corrupts' risks but, other than those, I don't know of any other risks associated with compacting. Conpacting can only be done with exclusive access to the database and, if the database is set to compact on close, it only compacts when the last user closes it

As for how often to compact..

Whenever you run a query or report, you're creating duplicate data (the data in your source table(s) and the data in your results set). The database file automatically expands to accomodate this and, when the query or report is closed, it is supposed to realize it and shrink back down. But that doesn't always happen or, at least, it doesn't always shrink back as far as it should

I have a database that's about 80 megs, have 2 or 3 users in it all day, every day and compacts on close. Compacting it trims 3-5 megs every day. If you can set yours up to compact on close, I'd do it. The only thing to remember is that the larger the database file, the longer it takes to compact. (My 80 meg database takes about 2 minutes.) The last person leaving the database may have Access take a minute or few to close (depending on the size of your database)

Hope this helps

Howard Brod


----- Bruce wrote: ----

I have learned recently (in this forum) about compacting
and repairing databases using the built-in utility. After
copying some databases, I have run the utility, which has
decreased the size of the databases, and maybe they run
faster now. All seems to be well with the compacted
databases. Is compacting a good idea? How often? Should
I set it up to run automatically each time the DB is
closed? If there is another user on the DB, will this
produce an enigmatic error message? Are there risks to
compacting? Anything else I should know about this
 
Thanks for the help.
-----Original Message-----
You always have 'if the application corrupts' risks but,
Click to expand...
other than those, I don't know of any other risks
associated with compacting. Conpacting can only be done
with exclusive access to the database and, if the database
is set to compact on close, it only compacts when the last
user closes it.
As for how often to compact...

Whenever you run a query or report, you're creating
Click to expand...
duplicate data (the data in your source table(s) and the
data in your results set). The database file
automatically expands to accomodate this and, when the
query or report is closed, it is supposed to realize it
and shrink back down. But that doesn't always happen or,
at least, it doesn't always shrink back as far as it
should.
I have a database that's about 80 megs, have 2 or 3 users
Click to expand...
in it all day, every day and compacts on close.
Compacting it trims 3-5 megs every day. If you can set
yours up to compact on close, I'd do it. The only thing
to remember is that the larger the database file, the
longer it takes to compact. (My 80 meg database takes
about 2 minutes.) The last person leaving the database
may have Access take a minute or few to close (depending
on the size of your database).
 
There is a substantial risk to NOT compacting. If you make periodic updates
to data, such as edits or updates, and you then experience a problem calling
for a database repair, the repair & compact could possibly undo data changes
since the prior compact. Assume that data entry/edits are not "permanent"
between compacts!

I, personally, compact each MDB after any modification to an object. For
data changes, compact at a regular scheduled interval. How frequently
depends on the size of the MDB, percentage expansion (due to activity) since
the prior compact, how busy you are otherwise, etc.

Compacting every couple years isn't often enough. Could be monthly, weekly,
or several times per day (based on database activity). I've never had a
problem doing frequent compacts.
 
Thanks for the additional information. I don't like to
accept Microsoft's assurances on such things unless I have
independent confirmation that they work as advertised.
Now I have that confirmation from several sources.
 
Back
Top