[AccessD] Back End Bloat - Revisited

Jim Dettman jimdettman at verizon.net
Mon Jan 5 10:22:39 CST 2009


Don,

<< Or does the DB engine perform activities as CPU cycles are available that
might result in bloat such that the observable jumps in size are not
synchronous with observable system activities?>>

  There is an undocumented switch that provides hooks into JET to get
detailed statistics on what the engine is doing, but the tools to use the
data don't exist outside of Microsoft.

  About the only thing available to anyone is the ISAMSTATS method.  You can
read about it here:

http://msdn.microsoft.com/en-us/library/aa188211(office.10).aspx

  Only offers very basic statistics though, but it might be enough to point
you in the right direction.

Jim.

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

Mark,

Exactly one.

The app runs as a sort of daemon detecting the source text files, evaluating
their contents, and cataloging and tracking them through their life cycles,
at the end of which it captures several summaries of the contents in a
separate (non-troublesome) DB.  In fact the system is configured to prevent
itself from being run in more than one session at a time.

The baffling thing is that the system has been running pretty much
trouble-free for several years, and only recently started doing the
"mega-bloat" thing.  I confess that I am more or less constantly tinkering
with it, but I can't think of anything that I've done recently that might
cause this behavior.  Of course, I don't have a clear understanding of all
the things that contribute to DB bloat - just the more obvious ones like
removing records and failing to set object variables to nothing.

I'm about to start hammering out a method for routinely compacting the thing
using automation, but I'd sure like to understand what's going on.

I did add a procedure that records the size of the BE (in a table that
resides in a separate mdb) at the beginning of just about every proc that
runs in the processing cycle.  So far, I've captured about 50K records that
may reveal whether there is any correlation between certain procs and
increases in db size.  Does anybody know whether that's a reliable way to
measure such a correlation?  Or does the DB engine perform activities as CPU
cycles are available that might result in bloat such that the observable
jumps in size are not synchronous with observable system activities?

Don



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Friday, January 02, 2009 8:08 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Back End Bloat - Revisited


Don,

I saw where you mentioned the FE was accessed via terminal server...and that
it was only "1 app"......How many users might be using the FE at the same
time?

Mark

----------------------------------------
> From: Donald.A.McGillivray at sprint.com
> To: accessd at databaseadvisors.com
> Date: Thu, 1 Jan 2009 23:47:13 -0600
> Subject: Re: [AccessD] Back End Bloat - Revisited
>
> Dunno, John.
>
> I suppose that our IT overlords would prefer that we be using something
other than Access. SQL server is on the list of approved alternatives, but I
don't know about Express.
>
> Will that integrate pretty seamlessly with my Access front end? What's the
learning curve like for somebody whose experience with databases is pretty
much limited to working with Access?
>
> Don
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Thursday, January 01, 2009 7:39 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Back End Bloat - Revisited
>
> Any chance you could just move this BE to SQL Server Express?
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> McGillivray, Don [IT] wrote:
>> Hi Gustav
>>
>> Got it. I checked a couple of tables and it is off for them. I've never
set that switch for any table, so I'm guessing that "off" is the default
position. Looks like that feature relates to replication, and there's none
of that going on with this database.
>>
>> BTW, the BE that is giving me fits holds meta data related to text files
containing data produced by other systems. This same app is attached to
another BE database that collects summarized data from those text files.
This second DB receives data only (no updates to records) and grows at a
rate that appears closely tied to the rate at which data is added to its
tables. This leads me to think that the bloating trouble that I'm having
with the meta data BE might be related to the updating of existing records
that the app does. I understand next to nothing about how Jet manages the
database, but does this hunch square with the normal function of Jet? I
would have expected bloat to occur in the front end, because that's where
all the code, queries, etc happen. Why does the BE experience bloat in the
sort of arrangement that I have described?
>>
>> I appreciate your willingness to share your expertise with me (and all of
us).
>>
>> Happy New Year!
>>
>> Don
>>
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
>> Sent: Thursday, January 01, 2009 2:44 PM
>> To: accessd at databaseadvisors.com
>> Subject: Re: [AccessD] Back End Bloat - Revisited
>>
>> Hi Don
>>
>> Off!
>>
>> /gustav
>>
>>>>> Donald.A.McGillivray at sprint.com 01-01-2009 23:41>>>
>> Thanks, Gustav.
>>
>> Yes, I did turn off the subdatasheets. What about row level tracking?
What are you suggesting for that? Switch it off if it's on?
>>
>> Don
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
>> Sent: Thursday, January 01, 2009 1:13 PM
>> To: accessd at databaseadvisors.com
>> Subject: Re: [AccessD] Back End Bloat - Revisited
>>
>> Hi Don
>>
>> Not much to add, but did you turn subdatasheet feature off for tables not
needing this feature? It is in table design view properties, while the
rowlevel tracking is found on the properties of the table in data view.
>>
>> /gustav
>>
>>
>>
>> --
>> 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
>
>
> 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
_________________________________________________________________
It's the same Hotmail(r). If by "same" you mean up to 70% faster.
http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_broad1_1
22008
--
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