What causes .mdb file to grow? (Access 97)

Topic

New Reply

Hi Lounge Lizards … I have been tracking the size of an .mdb file for about 6 months and there are huge spikes which are not related to increases in traffic or data. For instance, right now is our slowest period and the database size hit a new all-time high. I’m attaching a chart – you can see that we compact it once a week.

Our boss is asking, “Why?” when he looks at this chart … and, “I don’t have a clue.” is not the answer he’s looking for. Can anyone help????

Reply | Quote

Replies

Any time you edit a record, or add new records, the database grows. You said you compacted it every week, but it grew from week before last week to last week to this week – or was the measurement from last week taken before the compact was done? In any event, one thing you might try is importing everything into a new empty database. Also is your database split? If not you should probably do that – see our tutorial on why it’s a good idea. If indeed you’ve done all those things, then you might start looking at putting individual tables in different database – or – you may need to look at SQL Server. Hope this makes sense – if not post back.

Reply | Quote

Hi Wendell,
Yep – its split. I’m really interested in what you said about it growing every time you edit a record. You see, we’re not worried that its getting too big – over the last 6 mths it’s lowest point was 160MB and highest was 290 just yesterday and the trend line shows overall growth of only about 10MB. I just need a way to explain to the owner *why* the size goes up when overall activity on the database is assumed to be going down. I’d like to be able to say, “Don’t worry – this is just the way Access is” – I wish I’d never started charting the daily changes in size because its causing the client [probably needless] worry. On the other hand, this is the only way I know to figure out how fast it is actually growing.

Reply | Quote

It probably *is* just the way Access is.

If you have indexes on your tables (and if you don’t, Access will probably have created hidden ones), a seemingly simple edit of a record could necessitate a major overhaul of indexes. In such a case’, Access probably (I’m speculating now) expands the database to make room for a new version of the index, but it doesn’t release the space taken up by the old version. Similarly, if you have relationships with cascading updates, a simple change in one table can trigger a series of updates in other tables. ther is probably more in this vein.

Combined, it means that the impact of a small change can be negligible or substantial – it depends on the context of the change.

Reply | Quote

Another factor than can cause disproportionate increase in size of an .MDB file is refreshing linked tables programmatically. I have one application where certain linked tables (5 in all) are relinked to a different back-end .MDB using the DAO TableDef RefreshLink method. After running sub to relink tables, the database increases in size by almost 1.2 MB. When relinking same 5 tables “manually” using the Linked Table Manager, the db increases in size by only about 140 KB. In either case after compacting database it returns to original size. This behavior occurs in both A2K and AXP (A2K file format); I no longer use A97 so am not sure if this occurs in A97 as well. I have not seen any explanation for the disproportionate increase in size when using RefreshLink method.

Reply | Quote

Something that I have discovered lately while running QA tests on our Access application under Windows 2000 and Windows XP is that you do not need to actually change any data yourself for the database to grow. All you have to do is browse through the forms, click a few buttons, view some reports, etc – doing nothing that will actually change data and you will notice that the mdb will grow.

What happens, is that each time you access an object, eg. open or close a form, click a button, run a query, open or close a form, etc; the time and date of that action is recorded in the database. The size of the time and date stamp may also vary depending on the Windows Regional Settings.

We have the test results to prove it and it has caused pure and unadultarated torture for us in setting up a standard baseline system with which we can compare further tests and it took about 3 months to find this stupid information out, so I know exactly how frustrated you feel.

Reply | Quote

I saw an article somewhere (I have it at home) which showed by how much a db bloats when you do things such as change recordsources programatically, run queries etc.. Running queries will cause some bloat when the Rushmore optimisation kicks in. It has to be stored somewhere. I’ll try to find the article and post it or post a link to it. It was written by someone who developed a method of avoiding db bloat from temporary tables by using csv files instead of Access tables. HTH

Reply | Quote

Are you thinking of the article “Temporary Tables with No Bloat”, by Doug Den Hoed (Smart Access Jan 2001)?

Reply | Quote

Yep! Thats the one.
Good call Charlotte!

Reply | Quote

I built a demo based on that article, so I remember it well. Unfortunately, the demo is too large to post here.

Reply | Quote

Thank you all so much. Yes, it does use lots of indexes and cascading updates. Its No, Mark, its doesn’t have picture objects yet, but probably will later, and yes, quite a few temp tables that are created and destroyed using program code. And, yes, there is code to re-link linked tables programmatically. When I first started charting its size, I concluded that we’d have to move to SQL server within 2 years – now after charting for 6 mths, its clear we’re growing slowly and can stay in Access for years to come.

The .mdb has 95 tables, 534 queries, 87 forms, 59 reports, 6 modules and approx 20,000 lines of code. I’m going to summarize everyone’s comments above and conclude that the inexplicable increases in size are due to the large number of objects that gets affected even if users are only viewing data.

Reply | Quote

I believe you said before that the database was split? Where is the bloat occurring, frontend or backend? If in backend, can you move those temp tables to frontend? (they will probably work better there anyway, if frontend is on user’s local drive) In Access2000 and up, you can always have the database “compact on close” (very handy for frontend), or have the user automatically download a “fresh” copy of frontend each time they boot-up.

Reply | Quote

Mark makes an excellent suggestion regarding the creation of temporary tables, presuming that’s where you are making them now. Is your front-end located on the server, or is it located on each workstation? Also, how many users are you supporting? Depending on the answer to those questions, you may want to consider upgrading to SQL Server in any event. First, an MDB that grows to 260MB now, may well grow to 500MB before long, and once that happens things tend to get flakey, compacts take a long time, and so on. Second, SQL Server will give you better response, especially if you have large tables (say 100,000 records or more). Finally, you will have significantly less problems with stability and reliability. The down side is some additional cost, though it isn’t significant compared to the cost of Access for each user, the learning curve (which has been eased considerably with some books on SQL Server for Access developers), and the potential security issues you must deal with. One final question – do you anticipate upgrading to a later version of Access anytime soon? If so you might want to consider the SQL Server upgrade at the same time. Just some food for thought.

Reply | Quote

I’ll check to see where the temp tables are created. The front end is in each remote user’s folder on Terminal Server plus a few other front-ends distributed to workstations on our LAN. There’s about 12 remote users and 12 users on the LAN. Its likely the number of remote users will grow to 18 or 20 in 12 months. Our biggest table has 38,000 records. We’re not upgrading from Access 97 because of cost. We’ve thought a lot about going to SQL Server. One of the reasons we haven’t is because one of the original programmers on this project avows that we might slow down response time (already a bug-bear) unless we re-write all the queries as stored procedures. With all the queries we have, and all the lines of code, this is a daunting task.

Reply | Quote

Hmm – it seems to me there are some open questions about what sort of license is required to run Office apps via Terminal Server – in any event does MS offer some sort of group license for users in the Canadian market? There are some major advantages in using 2000 or XP from a performance and a programming perspective. One alternative would be to create runtime packages to deploy for each user, whether on the LAN or via Terminal Server. No license is require there.

If you are experiencing performance issues now, you should see a significant improvement in switching to SQL Server. We typically see a 10 to 1 improvement in performance using ODBC linked tables as long as all the tables are in SQL Server. You can see some performance degredation if you try to joing SQL Server and Access tables in a query, but that’s about the only case where it is an issue. We’ve run a substantially larger database (1.5GB with about 150 tables) with a front-end containing some 700 queries, 100 forms and 150 reports and gotten subsecond response from complex queries pulling dozens of records into a subform based on a table with over 1 million records, where the primary record came from a join on two tables of about 80,000 records each. The server was fairly robust – Dual PIV processors with 1GB of RAM and 30GB of hard drive – dedicated to SQL Server. In addition, there were some 70 users of the database, about 20 of which were reasonably heavy, and 4 did data entry all day long. The various front-ends used with this probably have between 50K and 75K lines of code. You might want to take a look at Alison Balter’s new book on Enterprise Development – she talks a fair bit about the pros and cons of various approaches, and what pitfalls to avoid.

In any event, I wouldn’t be too daunted by the task – you may find you need to rewrite a handful of your queries as views or stored procedures, but I would fully expect most of the queries would give you better performance than you are currently getting. If you want to dig into this further, let us know – I’m sure others would be willing to chime in with their experiences too.

Reply | Quote

Thanks Wendell. Your comments will have a significant effect on our long term strategy.

Reply | Quote

Steve – I found the article – http://www.larkfarm.com/sax/sax2-3.txt%5B/url%5D. Thanks.

Reply | Quote

How does the time stamp change size by regional settings? Time and dates are stored in the same format no matter what region of the world you are in (in Access). It’s a double variable, the whole number is the date (numer of days from Dec. 30th 1899), and the decimal number is the fraction of a day (0 is midnight, .5 is noon, etc).

Reply | Quote

Well that is actually something to ask Micro$oft.

Somehow the MS Developer who did that section must of had a double overload of coffee or something when (s)he did that section as it somehow stores the US date and US time as different sizes as compared to UK date and UK time.

Done ask me how or why – its just like that. Its only something which we just descovered in the last couple of weeks…..

Reply | Quote

That’s pretty odd. They must be storing strings, and not actual date/times.

Reply | Quote

Maybe there’s a language or format element that’s also being stored somehow. It wouldn’t have to be in the same field. The 2002 format is slightly weirder than the 2000 format as well and has still another system table that causes permanent bloat.

Reply | Quote

Hello all,

As noted earlier the creation/deletion of temp or working tables can significantly contribute to MDB bloat.

Access easily grows to accommodate the added record volume but of course does not clean-up after itself by shrinking to the appropriate size when the record volume is less.

Most active Access applications will have this problem and require a

Reply | Quote

If you search around in this forum, you’ll find discussions of using a separate mdb for temporary tables, Matteo. However, keep in mind that the queries themselves cause bloat, even without the temporary tables.

Reply | Quote

Only thing I know of that might apply is if you store a formatted date string using the “medium format”, dd-mmm-yy. Some languages use a 4-character month abbreviation for at least some months. French is one of those and we ran into difficulties with a medium date formatted string because we needed a wider text field. I don’t see any reason for it to affect database size, although I have seen the ridiculous growth in an XP database in 2002 file format. Just opening it seems to make it grow by a couple of megs. Add a 3 line function stub and it grows significantly. Just opening and looking around a bit and pretty soon your mdb has doubled in size without even making any changes in data, let alone in objects.

Reply | Quote

Additional note: As experiment, tried replacing old-fashioned DAO RefreshLink method with new-fangled ADOX code, using ADOX Table object’s “Jet OLEDB:Link Datasource” property to reset path for linked tables. Besides running noticeably slower, after same 5 tables were relinked using ADOX code, the .MDB had bloated in size fm appx 4036 KB to appx 6648 KB, an increase of appx 2.6 MB in repeated tests. That’s an awful lot of overhead to store 5 crummy little connection strings…. So if anyone is interested in knowing how to refresh linked tables in a slow, inefficient manner, see MSKB article ACC2000: How to Use ADOX to Create and Refresh Linked Jet Tables for more details.

Reply | Quote

Ah, but with ADO you can dispense with linked tables altogether … unless you want to use bound forms of course.

Reply | Quote

Just a couple of other thoughts to add to the suggestions from Wendell and Hans.

Do you have any objects in your database; that is, pictures, etc.? Do you have any temp tables, in which the data is frequently being deleted/added?

Reply | Quote