Database Size

[Rick West]

We are using Access 2003 with a FE/BE application. FE size is 94M backend size is 384M. We import nightly from excel to the BE db which then increases the BE db size alot, so we compact/repair right after the import.

We have read that each db can be up to 2G. So we're not in trouble yet but would like to know:

1) Is there a way to stop the db inflation during the import?
2) What size of db should we start getting concerned over? 1G? 1.6G? other?
3) Will archiving data from the tables (then deleting it) help significantly to reduce the db size?
4) If yes to #3 - is there a way to find the tables causing the biggest useage?

Thanks

[NTC]

I believe your issue is db management - - and not any flaw or problem with the db.

Yes, if you are growing that db BE via import of data that much you need to establish an archive logic & approach. This isn't actually an Access issue. It is common to the entire field of db management. No db can grow to infinity. Database management (not design or mining - just managing) - is a full time job in some mainframe environments because their transaction volume is so high - they are constantly moving data off line into archives.

There is no single answer as to whether to do this at 1G or 1.5G as it is possible you have complex queries going thru big tables that are too slow and can be improved by removing unnecessary records - - so the theoretical max can be irrelevant if the user experience gets bad before you get close. Just a note.

I would not look at it as to 'which table' is causing the problem. When you archive - you copy over and then delete records. This generally is date range oriented and will touch many tables that cross references. It requires some thought. You have to know your data and know which data is rarely if ever going to be needed again.

[Rawb]

1) Is there a way to stop the db inflation during the import?
There are two ways a database increases in size: addition of data (duh?) and what's called "overhead." It's likely that much of your inflation is caused by overhead, which can only be removed by running a Compact and Repair.

If your inflation really is caused by the data, I'd take a look at the import process itself as well as where the data ends up and in what format. Try to limit the field size to only what you absolutely need (use text fields instead of Memo if you're not dealing with large amounts of text, etc.) Also, make sure your data is normalized: Redundant data can cause rampant inflation.

2) What size of db should we start getting concerned over? 1G? 1.6G? other?
I'm with NTC on this one. Worry about database performance before raw size. Even small databases can suffer from performance problems if the data is heavily used/changed or if most of it is in a single table/area.

As for raw size, I'd start worrying about it as soon as you see the possibility that your DB could eventually break the 2GB limit. It's a lot easier to change a small database than it is to change a large one.

Again, I'd go with NTC's recommendation and try to focus on archiving old or infrequently used data in another file. If you still need access to it, you can even link it to the FE (you can have multiple BE files).

3) Will archiving data from the tables (then deleting it) help significantly to reduce the db size?
Depending on how much you archive, yes. Especially when you're working with a BE.

4) If yes to #3 - is there a way to find the tables causing the biggest useage?
Look for tables that use Memo or large Text fields. Memo and Text fields tend to be much bigger space hogs than other fields. Additionally, Access can tell you the file size of individual Tables.

[MFriend]

Hi Rick:

Another option for you might be to use Microsoft SQL 2008 R2 Express for your backend. It is free and will store 10 GB of data (and Access makes it pretty easy to export/import the Access database to it).

Matthew

http://www.microsoft.com/express/database/