Why would MDB filesize grow if no data change?

After compacting a particular database, its filesize grows from a user simply logging on, opening/closing a bound form (without changing data), and logging off.  For example: Post-compact size = 393,244 KB

- First LogOn/OpenAndCloseForm/LogOff increases size to 393,272
- Second LogOn/OpenAndCloseForm/LogOff increases size to 393,324
- Third LogOn/OpenAndCloseForm/LogOff increases size to 393,328

(Size stops growing at this point, regardless of any additional LogOn/OpenAndCloseForm/LogOff.)

This particular form displays 1,000+ actual records, and four bound subforms that also display actual records.  The mainform and subforms use query definition objects as their record sources.  Similar behavior occurs with other forms.

The environment is 64-bit Windows 7 hosting 32-bit Access 2010 and an unsplit, legacy 2002/2003-format MDB with ULS.

Is this behavior expected, given the internal mechanics of Access?  If not, what might be causing it?  Any reason for concern?
billparsAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
That is certainly expected. Access does some caching and other stuff when working so what you see is normal and nothing to care about.

If you want zero bloat, you can try to mark the physical file as Read-Only. This effectively forces such data to temporary files and - per definition - no bloat will occur. However, this will not work if your application modifies objects and saves these.

/gustav
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
As gustav said, expected.     While you have not done anything from your viewpoint, just the act of you opening and closing the DB file causes JET to read and write data.

JET has a number of internal operations where it writes to the database even if you have not.

Jim.
0
 
billparsAuthor Commented:
Gustav and Jim,

Thanks for your insight (and reassurance).  Before I award points, I would like to ask if this "caching and other stuff" might explain another observation.  (I will open another question if you think it appropriate.)  In the production environment, the following sequence occurred:

- User A opened a particular form and held the Page Down key to traverse 100+ records with fast performance.

- User B opened the same form, and did the same thing, with similarly fast performance; but when User A tried again (without closing and reopening the form), performance was painfully slow for them, while performance remained fast for User B.

- User A closed the form, reopened it, and got fast performance again; but now performance became painfully slow for User B.

On and on this went, with additional users playing along, as if each could "steal" performance from the others, by simply reopening the form and traversing records.  Might this be attributable to the way an MDB caches?

A couple of (extremely naïve) hypotheses I could formulate for the above observation, given caching:

1. The cache created for a given form by a given user is optimized for their particular system, connectivity, etc., and may be sub-optimal for other users.

2. When a user opens a form and loads data, it builds a new "active" cache for that form, and "deactivates" any caches previously built for that same form by other users.

Can either of you (or any other experts) offer any additional insights on this?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Might this be attributable to the way an MDB caches?

 That would more likely be locking.

 The cache is local to each machine as is all db engine operations and it's a cache of database pages.  Those pages may be records, indexes, long value pages (things like memo fields), or table definitions.   In regards to records, they may be yours or system ones.

 There are a number of settings that control how long various things are held in the cache and how JET performs record operations (if a record is changed, how quickly is it flushed to disk, does a certain type of operation cause an implicit transaction, etc).  There are also settings you set that control the placing of locks for example.

 One thing that's very important to keep in mind; with JET, the server is nothing more than a file sharing device.  All manipulation of the file is done by each of the clients.   That's why MDB/ACCDB's are prone to corruption more so than other RDBM's like SQL Server.   With no server side process, any abnormal disconnect does not allow things to roll back.

Jim.
0
 
billparsAuthor Commented:
Jim,

Thanks for the additional insights.  They leave me confused on two points:

1. The original post by Gustav implies the MDB filesize grows because of caching.  How can it, if caching occurs on the local machine?  Have I misinterpreted his post?

2. When users were "competing" with each other for performance, no data were changed; users simply displayed data as they traversed records.  In fact, the mainform controls are locked (and AllowAdditions, AllowDeletions, and AllowEdits are False on subforms) unless the user clicks a mainform button to allow changes to the current record.  How do the mechanics of locking lead to the observed behavior?

Bill

PS: Should I open a new question on this?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<How can it, if caching occurs on the local machine?  Have I misinterpreted his post?>>

  It would be more correct to say that it carries out temp operations that require writing to disk.  For example, if you request a snapshot recordset, that means a copy of the complete record must be made.  Depending on size, that might be stored in the DB, or written out to a separate temp file.  The inner workings of JET have never fully been documented, but the Cache for the database engine is strictly memory based.  You can however see temp files show up on occasion.


<<When users were "competing" with each other for performance, no data were changed; users simply displayed data as they traversed records. >>

  Yes, but the database system still needs to know someone is touching that record, so even though your not doing anything, it still gets a read lock.  That delves into multi-user issues and keeping records refreshed.

 There are numerous layers here too; disk, server OS, network, and your station.  It's hard to point to a specific operation and say what exactly is causing a slowdown to happen, but locking is probably it from what you've described.

Jim.
0
 
billparsAuthor Commented:
<<It's hard to point to a specific operation and say what exactly is causing a slowdown to happen, but locking is probably it from what you've described.>>

Locking is a pretty broad concept.  In this case:

- Access Options | Client Settings | Advanced | Default Record Locking = No Locks
- Access Options | Client Settings | Advanced | Open Database By Using Record Level Locking = Checked
- Main Form and Subforms | Property Sheet | Data Tab | Recordset Type = Dynaset
- Main Form and Subforms | Property Sheet | Data Tab | Record Locks = No Locks
- Main Form and Subforms | Record Source | Query Definition | Property Sheet | Recordset Type = Dynaset
- Main Form and Subforms | Record Source | Query Definition | Property Sheet | Record Locks = Edited Record
- AutoExec Code | DAO.DBEngine.SetOption Option:=DAO.SetOptionEnum.dbMaxLocksPerFile, Value:=CLng(10 ^ 9)

Do any of these jump out as obvious issues, or as places to begin tweeking/testing?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
There's no right or wrong answers there, just depends on what you want.

 For example Default Record Locking on a form controls the type of lock.  "No locks" does not mean that there is no locking, it means "No Edit locks" or in other words, optimistic locking will be performed instead of pessimistic.  The difference between the two is when a lock is placed.  With the first, it's only when the user actually goes to save the record.  With the second, it's as soon as a record starts to be edited and it's not released until the record is saved or undone.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and by  the way, it's certainly time for a new question(s).

Jim.
0
 
billparsAuthor Commented:
Thanks Jim.

New question posted here.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.