Database filesize growth

[tehNellie]

Sooo, I've inherited a database application using access, nothing flash, a couple of tables, a few thousand records per table and a bunch of forms/queries driven by Macros (shudder).

Once a week I get an export of a few hundred rows, max, that I drop into this database.

Several people log into the database (no FE/BE at the moment) and do things to these records which adds some rows to a related table and changes a couple of fields in the main table.

The bit that has me raising my eyebrows is that two weeks after inheriting this DB it's doubled in size from a wholesome 20mb to 40mb.
Now am I just noticing this 'bloat' and performance hit more because it's a relatively small filesize database to begin with and Access would be adding about 20mb of crap to a DB in a week or so whether it's 10mb or 500mb in size or is there something fishy going on that I should be looking for?

I've had a little bit of a look around, and other than C&R it regularly (thanks Captain obvious) I've not seen much to explain the bloat in Access and whether this level of file growth is unusual. I do know that this DB will grow to be at least 300mb if I let it, because that's how big it was when I inherited it .

 

[KenHigg]

I think this growth is for the most part 'normal' for Access. The extent to which it grows depends on what is happening inside the db. Like if you are creating records in temp tables and deleting them, etc, this will cause it to grow. So there may be some design things you can do to slow it down but in the end you need to monitor it. You can add a compact on shut down routine and I think you can even decompile the application and help. Link

 

[MicroE]

You need to split the db and place the FE on each user’s local PC.
http://www.myaccesstips.com/deploy/deploy.html

Design change and action queries will create bloat.

 

[tehNellie]

Believe me the FE/BE split is first on my "to do" list, I've just got a few more pressing issues elsewhere to deal with.

The extent to which it grows depends on what is happening inside the db. Like if you are creating records in temp tables and deleting them, etc, this will cause it to grow. So there may be some design things you can do to slow it down but in the end you need to monitor it.

As far as I'm aware there's nothing in there shifting things into temporary tables, it's effectively a work package system tracking people leaving the company, so you log against an employee all the system accounts you've switched off for them and then flag it as closed.

The structure is basically a parent table containing the employee information and a child table which has one row per system account containing the login, the system and a couple of other bits of information. At least, these are the tables where information is being added/altered.

It was just the intial extent of the growth on such a small db (all my other databases are 500mb+ where a 20mb growth is more or less unnoticeable) that came as a bit of a surprise and had me wondering whether there might be something untoward going on.

 

[KenHigg]

If this is really high on your issues list you could compact it then step through each of the normal tasks and take note of the ones that seem to have the biggest impact. Do some task, take note of the growth, do another task and take note of the file size, etc. Then post back here and see if any one has any ideas on the specific issues...

 

[tehNellie]

I guess I'm chewing the fat and just getting some opinions as much as anything, the obvious solution is to just C&R it regularly. It's just one of those things that when you come across it in a different context (I'm well aware access dbs grow etc etc.) than what you're used to just sort of slaps you about the face and you realise you have no idea whether you are looking at something "wierd" or just Access being Access.