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