DB expands rapidly but compacts back to earlier size

  • Thread starter Thread starter KARL DEWEY
  • Start date Start date
K

KARL DEWEY

My DB is 2000-2003 on XP machine but being opened and worked using Access 2007.
In testing it seems to be caused by running reports.
Any ideas what to check for?
 
I rather doubt running reports is the cause. A database usually increases in
size when "Action" queries are run or there are large imports done. Access
creates a buffer for any record being updated, inserted or deleted. This is
so you can cancel out of the query and leave the original data as is.

This buffer causes fragmentation within the database. The result is empty
space. Compacting removes most if not all of that space.

Something you might try is to import everything into a new database. This
usually defragments the code pages.
 
KARL DEWEY said:
My DB is 2000-2003 on XP machine but being opened and worked using Access
2007.
In testing it seems to be caused by running reports.
Any ideas what to check for?

There are many causes for database bloating, including the common one that
comes to mind almost immediately, deleted records. But, running certain
queries, making design updates, etc., and other (unobvious) activities can
use worktables in their processing, and that storage, too, is not recovered
until a Compact and Repair is run. From the reports about bloating in
Access 2007, it would seem that it may lose more memory in this way than
previous versions.

I know of no documentation of memory-loss-idiosyncracies of Access 2007...
so can only suggest the usually recommended approaches, already
well-documented here in the newsgroup

Larry Linson
Microsoft Office Access MVP

Larry Linson
Microsoft Office Access MVP
 
Jet is too bloated for real world use.

Move to SQL Server
SQL Server Express and MSDE have 'auto-shrink' set to true by default

-Aaron
 
Back
Top