Fundamental MDB bloat issue

[Mike Oliver]

I'm trying to track down a problem we're having with our MDB growing
in size. We have a Windows service that is accessing a MDB constantly.
Part of what this service does is "refresh" the contents of a table by
deleting all records and then inserting into it. This is a process
that happens periodically over the course of a day.

It seems that the simple process of deleting and inserting records in
a table where the overall number of records _does not_ change causes
the MDB to grow over time in a linear fashion. Is this true?

Is it a true statement that Jet/Access will not free up the space used
for these deleted records? If so, that means that the only way to
address this MDB bloat issue is to periodically compact the MDB
programmatically, right?

Thanks,

Mike Oliver

 

[Brian]

I'm trying to track down a problem we're having with our MDB growing
in size. We have a Windows service that is accessing a MDB constantly.
Part of what this service does is "refresh" the contents of a table by
deleting all records and then inserting into it. This is a process
that happens periodically over the course of a day.

It seems that the simple process of deleting and inserting records in
a table where the overall number of records _does not_ change causes
the MDB to grow over time in a linear fashion. Is this true?

Is it a true statement that Jet/Access will not free up the space used
for these deleted records? If so, that means that the only way to
address this MDB bloat issue is to periodically compact the MDB
programmatically, right?

Thanks,

Mike Oliver

Absolutely right on every point, except that you don't necessarily have to
compact it programmatically. You can do it using Access (which has a
command line option to do it if you want to run it as a scheduled job), or
using the Jetcomp utility which you can download from Microsoft.

 

[Dirk Goldgar]

I'm trying to track down a problem we're having with our MDB growing
in size. We have a Windows service that is accessing a MDB constantly.
Part of what this service does is "refresh" the contents of a table by
deleting all records and then inserting into it. This is a process
that happens periodically over the course of a day.

It seems that the simple process of deleting and inserting records in
a table where the overall number of records _does not_ change causes
the MDB to grow over time in a linear fashion. Is this true?
Yes.

Is it a true statement that Jet/Access will not free up the space used
for these deleted records?
Yes.

If so, that means that the only way to
address this MDB bloat issue is to periodically compact the MDB
programmatically, right?

Right. Either that, or change the nature of your processes. Option 1:
maybe, instead of having a local table that you periodically refresh
from another data source, you could link to the data source that you are
refreshing from and use that directly, instead of the local table.
Option 2: maybe you could update the existing records in the local
table, rather than deleting them all and replacing them. I don't
actually know whether this second option would conserve space or not,
but it would be worth a try.