[AccessD] Back End Bloat - Revisited

Jim Dettman jimdettman at verizon.net
Thu Jan 1 14:17:44 CST 2009


Don,

  What I was thinking about was to different situations:

1. When updating the records, records became too large for the available
free space on a page and would be moved to a new page. If you were doing a
lot of updating, this could cause the database to grow quickly.

2. More then one process appending records to a table, causing contention
for the last page.  Before JET 3.5, records were always added to the last
page.  This cause a lot of page contention with multiple users, so JET was
modified to always jump to a new page if the last page was locked rather
then wait.  This got rid of the contention, but it also results in a lot of
wasted space if more then one process is adding a lot of records to the
table, as each process will almost always end up using a new page.  The
database will bloat quickly if this occurs.  This is especially true for
smaller record.  Ie. instead of 10 records per page, you end up with 10
pages used each 10% full. 

  Someone has already suggested checking the JET version and I would make
sure you have the latest, but I know that you also said that updates may be
problematic given the environment.

  Only possible work around that comes to mind is to make sure you have no
open connections to the backend, then compact it via code.   I did this once
with a time clock polling app I wrote which was up 24x7.  I scheduled a
compact in my code at 2:00 am each morning, which kept the database from
growing even though a lot of records were added/deleted through out the day.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
[IT]
Sent: Thursday, January 01, 2009 2:52 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Back End Bloat - Revisited

Hmmm.  Not sure what you mean, but all the updating/appending happens in one
app.  There are several procedures in that app that update or append data.
The app is adding to/updating several different tables in various ways
depending on the type of processing underway.  Other apps link into the
data, but only for reading - no updating or appending.  I did shut down all
the other apps and just let the main one run (adding and updating), and the
bloating behavior still occurred.

Don


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Thursday, January 01, 2009 10:32 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Back End Bloat - Revisited

Don,

  Do you have more then one process updating/appending records?

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
[IT]
Sent: Thursday, January 01, 2009 1:06 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Back End Bloat - Revisited

Jim,

It's a little of both - some updating and some appending, and almost no
deletion.

Running Access 2003 (SP3), with Jet 4.0.  Using the Access 2000 file format,
and it's running in a terminal session hosted on a Windows 2003 server.

Thanks!

Don

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Thursday, January 01, 2009 9:35 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Back End Bloat - Revisited

Don,

<<The app can run for several complete processing cycles (each cycle
involves the collection of data from text files produced by other systems)
with no weight gain - NONE, and then it will suddenly leap in size by as
much as 100 MB.  Within a couple of hours, it will have grown from about 150
MB to well over 500 MB.  Then within 24 hours, it will have grown to a size
approaching 2 GB, requiring a compact.  The last time I ran through this
routine, the compacted size was just 8K larger than the size it started out
at before it started growing.>>

  Are you updating existing records or just appending?  And what version of
Access/JET is this?

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
[IT]
Sent: Wednesday, December 31, 2008 9:05 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Back End Bloat - Revisited

Hello All

A couple of weeks ago I posted a question concerning a bloating problem in
the back end database used by my application(s).  Thanks again to all of you
who took the time to respond.  I implemented all the solutions that were
suggested, but still the problem persists.  So, here I am again with hat in
hand . . .

The problem in a nutshell:

The back end database contains nothing but tables - no queries, no forms, no
reports, no code of any kind.

With a very few minor exceptions (~150-200 8-field records/month) data is
only added to the tables - not removed.

There are two applications that access the back end around the clock - one
gathers data from other sources and populates the system tables, and the
other provides a user interface which retrieves the data via reports and
various forms bound to the system data.

Most data is added to the tables by means of either saved insert queries or
SQL built on the fly and executed using "db.execute" statements in my code.
All such queries/code reside in the front end application mdb.

I have examined my code thoroughly, and can verify that there are no DAO
recordset objects created in the front end that are not subsequently set to
nothing, the setting to nothing occurring always in a post error handler
exit routine embedded in each procedure.  All DAO recordsets are being
closed before being set to nothing.

The BACK END is where I'm having the problem - NOT the front end.  (There is
some insignificant growth in the FE, but it's the BE that is off the
charts.)

The app can run for several complete processing cycles (each cycle involves
the collection of data from text files produced by other systems) with no
weight gain - NONE, and then it will suddenly leap in size by as much as 100
MB.  Within a couple of hours, it will have grown from about 150 MB to well
over 500 MB.  Then within 24 hours, it will have grown to a size approaching
2 GB, requiring a compact.  The last time I ran through this routine, the
compacted size was just 8K larger than the size it started out at before it
started growing.

I'm getting desperate here.  Still hoping that somebody can suggest an
approach that will stop the madness.

Many thanks!

Don McGillivray

This e-mail may contain Sprint Nextel Company proprietary information
intended for the sole use of the recipient(s). Any use by others is
prohibited. If you are not the intended recipient, please contact the sender
and delete all copies of the message.


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


This e-mail may contain Sprint Nextel Company proprietary information
intended for the sole use of the recipient(s). Any use by others is
prohibited. If you are not the intended recipient, please contact the sender
and delete all copies of the message.


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


This e-mail may contain Sprint Nextel Company proprietary information
intended for the sole use of the recipient(s). Any use by others is
prohibited. If you are not the intended recipient, please contact the sender
and delete all copies of the message.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list