Size of File Real Small after Compact

[Egad]

I copied did a Microsoft Access file and then did a Compact and Repair on it to see what would happen since I'd never done this before. It went from 20,116 KB to 1,988 KB. Is this right? It looks like everything is still in there but I'm hesitant to rename the copy to the original. Thanks.

 

[Travis]

This is correct. What this process does is removes all of the extra space that the MDB took up but never gave back. Databases are notorious space hogs. When you run the program it will converts the Code into Machine language. It does not necessarily remove the old machine code. Also if you have added records and deleted records constantly from the MDB this will also increase the size, but not reduce the size.

But it is not bad practice to hold the original as a backup (Burn it to a CD).

 

[Egad]

Thanks

Thanks, Travis. I've only been working with Access for a few years and just off and on so I try to be careful when I try new things. I've noticed that a database I did two years ago now looks cluttered and inept to me. I've got to say that I've learned so much from this forum and really appreciate the help of all of you. It's making me use the program so much more efficiently. Thanks!

 

[The_Doc_Man]

Welcome to the world of database bloat.

All MS software has the problem of bloat because they tend to not re-invent wheels. They all use pretty much the same memory reclamation software inside the programs. This "garbage collection" software does not attempt to dynamically reclaim used space. They leave it around for the next compaction. Anything you do slowly adds space to the DB file. There must be literally dozens of things you can do that will claim temporary space and then release it. But released space is merely MARKED for release. It is not actually released until you run a compaction process. So if you say you have not done this before, I believe the reduction you described.

Now, here's a way to make that into something practical. Don't compact again for a little while. (Only a little while, though.) Now every day or so, record the date & time vs. the size of the DB file. Take at least a few days worth of data, maybe even a couple of weeks worth. Do a compaction. Take another reading of size/date post-compaction.

Plot size vs. date or run a linear regression. The slope of the daily data is your db's GROWTH RATE - the rate at which the DB grows overall.

After each compaction, note DB size. Eventually, after several such compactions, you will get a SECOND plot, which is the actual DB growth rate. The difference in rates, when expressed in the same time units, probably Kb/day or Kb/week = OVERALL GROWTH - ACTUAL GROWTH = BLOAT RATE = the rate of Access building discardable data structures that make it to the disk, thereby contributing to that difference in pre- and post-compaction sizes. From this information, you might be able to project certain critical times to do disk, management.

 

[vangogh228]

Now, if only I could convert and compact my body....