Jet database growing and a few questions

[David]

Hello,

I have several applications that maintain a Jet database
via ODBC. There are perhaps two dozen tables, ony two of
which have perhaps 10,000 records each. The rest are
rather small. A typical MDB file runs between 1 and 4 MB.
They can be Access 97 or Access 2000 format. The
applications have been around for years. The DAO methods
are used to access the tables. During start up the Jet
interfaces are used to verify the database has the correct
schema and make the appropriate changes. Restarts are
rare. Many systems go for months without a need for
restarting. Most systems get a reboot whenever the Windows
Update cycle is required for critical updates.

Around April this year two customers (of several thousand)
have reported problems with their applications failing.
Upon examination, the database started growing rather quickly
even though the application logs show low activity. One
customers 4MB file grew to 250MB in two months and another
with a 2MB file grew to 1GB. To say the least, I was a bit
amazed. The applications monitor systems for alarms and
typically cause a few dozen add/updates to occur per month.

I've identified a reported problem that matches this
description. The only solution appears to be to close the
applications once in a while, say monthly, and compress and
repair the database. I can handle all that as well as
continue to verify that one of us didn't inadvertantly
cause the problem.

I'd like to know if there is a way to determine when
a Jet database is likely to have a problem. Is there a
way to ask Jet if it is starting to get fragmented or
corrupted? The only thing we can think of at the moment
is to watch the database size and perhaps take action
if the database seems to grow for no reason. As this
is a normal occurance for a freshly compressed database
what might be reasonable thresholds.

If you have seen this type of problem, or read about
it, was the increase in size rapid or did it occur over
a long period of time?

Thanks in advance,

David

[david epsom dot com dot au]

You should be checking the Jet DLL versions. Sometimes
particular versions of Jet have had bugs that cause this
kind of behaviour.

Also, bloating is associated with shared files. If
you decide that you will use exclusive access, you
should enforce that.

Finally, some bloating was associated with use of record-
level locking, instead of page locking. You may wish to
check which kind of locking is in use at each site.

I'm not aware of any way to check if an MDB is fragmented
or corrupted or bloated, other than to watch the file size,
and check the effect of compacting.

(david)
"David" <Fl************@United.Com> wrote in message
news:rOdGr40LMPU3-pn2-wL1dJi8vEm9y@localhost...

Hello,

I have several applications that maintain a Jet database
via ODBC. There are perhaps two dozen tables, ony two of
which have perhaps 10,000 records each. The rest are
rather small. A typical MDB file runs between 1 and 4 MB.
They can be Access 97 or Access 2000 format. The
applications have been around for years. The DAO methods
are used to access the tables. During start up the Jet
interfaces are used to verify the database has the correct
schema and make the appropriate changes. Restarts are
rare. Many systems go for months without a need for
restarting. Most systems get a reboot whenever the Windows
Update cycle is required for critical updates.

Around April this year two customers (of several thousand)
have reported problems with their applications failing.
Upon examination, the database started growing rather quickly
even though the application logs show low activity. One
customers 4MB file grew to 250MB in two months and another
with a 2MB file grew to 1GB. To say the least, I was a bit
amazed. The applications monitor systems for alarms and
typically cause a few dozen add/updates to occur per month.

I've identified a reported problem that matches this
description. The only solution appears to be to close the
applications once in a while, say monthly, and compress and
repair the database. I can handle all that as well as
continue to verify that one of us didn't inadvertantly
cause the problem.

I'd like to know if there is a way to determine when
a Jet database is likely to have a problem. Is there a
way to ask Jet if it is starting to get fragmented or
corrupted? The only thing we can think of at the moment
is to watch the database size and perhaps take action
if the database seems to grow for no reason. As this
is a normal occurance for a freshly compressed database
what might be reasonable thresholds.

If you have seen this type of problem, or read about
it, was the increase in size rapid or did it occur over
a long period of time?

Thanks in advance,

David

[David]

Thank you (david),

I've checked the MSJET40.DLL versions on all systems.
The two systems do have a second client that is attached
via a shared drive. I'll see if I can find out more
about shared files affecting the problem. I hadn't
checked the locking modes of affected systems. There
is a good possibility that the remote client machines
are set up for a different lock mode.

David

On Fri, 17 Dec 2004 05:48:13 UTC, "david epsom dot com dot au"
<david@epsomdotcomdotau> wrote:

You should be checking the Jet DLL versions. Sometimes
particular versions of Jet have had bugs that cause this
kind of behaviour.

Also, bloating is associated with shared files. If
you decide that you will use exclusive access, you
should enforce that.

Finally, some bloating was associated with use of record-
level locking, instead of page locking. You may wish to
check which kind of locking is in use at each site.

I'm not aware of any way to check if an MDB is fragmented
or corrupted or bloated, other than to watch the file size,
and check the effect of compacting.

(david)