Rapidly expanding Access datafile

[Informate-Rod]

I have a small Access 2000 app that whilst working is causing some concern.

The app is a VB module that is constantly running. The module imports lots
(30000+/day) of simple CSV style files, applies business rules to the raw
imported data and updates tables in an attached SQLServer 2000 database under
transactional control. The raw imported data is deleted before new data is
imported. An ODBC connection object is established at App startup and the
same connection is used to perfom all accesses to the SQLSrvr repository. The
app should run 24*7. The app is activated by calling it from an 'AutoExec'
macro that automatically runs on startup.

The app runs fine except that the size of the access file grows rather
alarmingly quickly. i.e. 3->14Mbyte in 2 hours. If you terminate and merely
'Compact & repair the database' the size goes back to the original 3 Mb.

I've doublechecked the code looking for obvious problems - making sure all
recordsets get closed etc - everything looks fine.

VB is not my normal language so I'm a little wary that I've ballsed-up;
looks and feels like a classic memory leak but I do not know memory
alloccation rules in VB.

The only other thing that might be a problem is if there are issues with
FileImporting using FileImportSpecs.

Anyone got any bright ideas??

cheers

Rod.


..

 

[Jesse Aviles]

As you delete records, Access keeps some "trash" that inflate the database
size. The way to delete the "trash" and reclaim the space is compacting the
database regularly.

Jesse Aviles

 

[Douglas J Steele]

The fact that you're importing, then deleting, the raw data is definitely
going to contribute to the growth.

Try linking to the spreadsheets, rather than importing, then updating your
tables.

If you need to import then delete, consider using a temporary database. Tony
Toews shows one way to do this at
http://www.granite.ab.ca/access/temptables.htm

 

[Albert D.Kallal]

VB is not my normal language so I'm a little wary that I've ballsed-up;
looks and feels like a classic memory leak but I do not know memory
alloccation rules in VB.

Hum, since when did a memory leak contribute to a data file? Can you think
of any memory leak in software that contributed to a data file size
increase?

I would not consider a virtual memory swap file (which most certainly would
increase in size due to a memory leak) an data file, and thus I can't
imagine any kind of leak that corresponds to memory.

However, not giving the JET database engine a chance to clean up things can
be source of trouble.

However, the architecture of jet is a frame based "bucket" system where
records are stuffed into frames. If the record goes *slightly* over the size
of the bucket, then a whole new frame needs to be added to the database.
This means that adding 5 byes of data to a record can cause it to spill over
into the next frame (and thus a 500 byte increase would be seen). You do
this over 1000's of records, and you got some SERIOUS increase in size.
However, now that those 90% empty frames have been added, then the rate, or
bloat increase settles down to near nothing. However, this don't sound like
your case.

Your case is that of any record added to a file, and then DELETED does NOT
reclaim space. If you can imagine 5 users working on the file at the same
time, and the byte offset changing when ONE user deletes a record? How can
we shrink the file with more then one user in the file? Further, if we have
a 500 meg file, and delete the 2nd record, are we now to pull the 499 megs
of data down one record to fill in that gab? (great idea...delete one record
of 100 characters..but generate 500 megs of disk i/o as we move the file
down to fill in this hole).

I think you can rapidly see that moving, or compressing and modifying the
actual location of where disk records resolve to in a multi-user system (the
JET mdb files are multi-user) is not going to work at all. This "problem"
applies to all database systems, and even BEFORE 20 years ago and the "pc"
age, this is a classic computer problem that all systems (and developers)
have had to deal with.

Last, but not least, for the last 20 years, be it old dbaseII, FoxPro, and a
good many of the other pc based systems, they all required a "pack" command,
or some type of command or maintenance that usually involved "copying" the
file, and leaving the hole behind. (and, this process could not, and did not
occur when the file was in use by more then one user). You will find that
the mainstay corporate database systems also function this way.

So, some of the solutions are:

Avoid writing code that creates temp data, and then deleting that data.

Write code that re-cycles existing records/data. Often a minor change in
design can eliminate the need to delete records. A good example is that I
built my own custom locking table. Since for each record+table I opened, I
needed to add a locking record to my "locks" table, I used a design that
recycled records (the user name + work station was used, but I never deleted
a locking record, but simply empted the one field that contained the lock
info). When I needed a lock, I searched for a blank lock record..and ONLY if
none, did I add a new record. However, I never deleted records in this
table..and thus my design avoided database bloat complete.

Since it sounds like you are importing data..and then deleting the data, the
solution in your case is to create a new "temp" mdb file that you discard
after you are done. And, the other possible solution is to read the data,
and re-process each record, and then write it out to sql server, and NOT use
a intermediate mdb table. If you link a table to sql server, and use the
"old" "open" command to read in the text file..the you don't need a temp
table in the mdb.