UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

2 Pages V   1 2 >  
Reply to this topicStart new topic
> db bloating under Access2007    
 
   
MarkLiquorman
post Oct 25 2008, 02:38 PM
Post #1

UtterAccess Guru
Posts: 578
From: Florida, USA



The attached db was created in Access2003. When run under Access2007 it bloats at the rate of about 4k every 5 seconds.

If you let it bloat for say 2 minutes, then close/re-open the db, it will start bloating again at about the 2 minute mark (essentially where it left off before).

I did notice that when running it from a thumbdrive that the little thumbdrive light blinks continually. I've tried other databases, and some don't bloat (no blinking) and some do (blinking). This is not related to the thumbdrive, as it happens from system hard drives also. And it doesn't "blink" using Access2003. And with 2 versions of Access on same computer, it only happens with A2007 version!
Attached File(s)
Attached File  EmptyDatabase.zip ( 15.97K ) Number of downloads: 6
 
Go to the top of the page
 
+
truittb
post Oct 25 2008, 06:47 PM
Post #2

Retired Moderator
Posts: 13,544
From: Texas (Is there anywhere else?)



Mark

I tried it on my laptop running Vista and Access 2007 and did not get any bloat. I only have the one version of Access installed.
Go to the top of the page
 
+
ClintC
post Oct 25 2008, 07:05 PM
Post #3

Microsoft Corp UA VIP
Posts: 205
From: Redmond, WA



I recently heard about this bug. Expect to see a fix for it in SP2.
Go to the top of the page
 
+
MarkLiquorman
post Oct 25 2008, 10:01 PM
Post #4

UtterAccess Guru
Posts: 578
From: Florida, USA



Where did you here about it? I hadn't seen anything anywhere else.
Go to the top of the page
 
+
truittb
post Oct 25 2008, 11:04 PM
Post #5

Retired Moderator
Posts: 13,544
From: Texas (Is there anywhere else?)



He gets all the Bug reports, public and private, since he is the Lead Programmer for the Access Development Team at Microsoft.
Go to the top of the page
 
+
MarkLiquorman
post Oct 25 2008, 11:11 PM
Post #6

UtterAccess Guru
Posts: 578
From: Florida, USA



Like I said, I hadn't seen anything about this anywhere. We are on verge of calling Microsoft, so I'd really like to know if this is an acknowledge problem, any if there is any work-around.
Go to the top of the page
 
+
truittb
post Oct 26 2008, 12:14 PM
Post #7

Retired Moderator
Posts: 13,544
From: Texas (Is there anywhere else?)



He said it was and would be fixed.
Go to the top of the page
 
+
zocker
post Oct 27 2008, 07:03 AM
Post #8

Utterly Eccentric and Moderator
Posts: 3,776
From: Bristol / Ipswich / Spain



Just for my own interest, are there any pics or BLOBS stored in your database.

Thanks
Go to the top of the page
 
+
MarkLiquorman
post Oct 27 2008, 07:34 AM
Post #9

UtterAccess Guru
Posts: 578
From: Florida, USA



The sample database attached to my first post was empty! No tables, no queries, no forms, no macros, no modules.
Go to the top of the page
 
+
MarkLiquorman
post Oct 27 2008, 07:57 AM
Post #10

UtterAccess Guru
Posts: 578
From: Florida, USA



Is anything known about this bug? That is, anything that might help us isolate when to expect to see this problem. as it doesn't seem to happen with everyone? I do work for a software house that has a software package that has been distributed to several hundred clients, each with from only a couple to maybe 20 users. There are all sorts of combinations of Access versions, O/S versions, etc. It would be helpful to be able to send-out a warning to users to look for this bloating in certain circumstances, and to give them guidelines for coping with it (such as exiting and compacting the db every few hours, etc.).

This is important, because while my sample EmptyDatabase bloated at rate of 4k every 5 sec, the real database (100MB) bloats at rate of about 256k every 5 seconds!

Edited by: MarkLiquorman on Mon Oct 27 9:36:13 EDT 2008.
Go to the top of the page
 
+
zocker
post Oct 27 2008, 11:05 AM
Post #11

Utterly Eccentric and Moderator
Posts: 3,776
From: Bristol / Ipswich / Spain



As I dont have A2007 I did not download it, I was thinking about possible future scenarios. Thanks!

Z
Go to the top of the page
 
+
bnelson333
post Nov 4 2008, 02:48 PM
Post #12

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



Clint, are you aware if the known issue is bloating in the FRONT-END? I have the same problem, but the bloating only happens in the front-end. The fe can start as small as a MB or two and easily be upwards of 20+ in a few hours. If left unchecked, it never seems to stop growing (though I usually compact it at around 20 MB, then it goes back down to 1 or 2). It seems to happen faster if more than one user is in it at a time (this is a multi-user, shared db, on a network drive -- I know, not ideal, but it is what it is until we get it on SQL).

EDIT - I just realized how much I contradicted myself here, talking about seeing the bloating in the front-end but having a non-split db. One of the things we tried to resolve the bloating issue was splitting it, and we saw the bloating happening in the front-end. We ended up leaving it as non-split because the split db was way too slow for users to use effectively (maybe another bug?).

Mark, I had the same problem when we converted to 2007. I racked my brain for over a week, completely tearing down and rebuilding my database trying to find the problem.

I'm talking, exporting all objects as text, re-importing, fixing corruption, decompiling/recompiling, rebuilding the back-end from scratch, disabling ALL code, stripped down to only the few basic queries required, stripping temporary querydefs, turning off autocorrect, anything I could find, I did (this is probably not an exhaustive list, just what I can recall right off hand). I never did find the probem and resigned myself to compacting & repairing every few hours.

Edited by: bnelson333 on Tue Nov 4 14:52:31 EST 2008.
Go to the top of the page
 
+
pinewild
post Nov 6 2008, 10:40 PM
Post #13

New Member
Posts: 1



Mark,
I noticed when moving to access 2007 that one of my databases became bloated. The other did not. The bloated one used the linked table manager to access tables from the other db. My bloated db started at 2 mb and a week later it was 45 mb with hardly any additional data. I'll be interested to see what the answer is.
Mary
Go to the top of the page
 
+
bnelson333
post Nov 7 2008, 07:45 AM
Post #14

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



The database that we have this problem with, front-end + back-end in same file, starts at about 20 MB after a fresh compact. After use, it grew to about 25 MB and pretty much leveled off. It would grow more, but very slowly after time (as users add data).

Since 2007, it went from 20 MB to 100 MB+ in just a day or two. I watch it every day, after about half a day it's usually up over 50 MB, so I compact it, it goes down to 20, rinse and repeat.

We're currently working to migrate it to SQL so we gave up on trying to solve the bloating and are just managing it until we're converted.
Go to the top of the page
 
+
MarkLiquorman
post Nov 8 2008, 09:51 AM
Post #15

UtterAccess Guru
Posts: 578
From: Florida, USA



We have official confirmation that a fix is on the way!

The company I contract with finally called Microsoft about the problem. This was more than a minor annoyance to them, as the bloating speed seemed related to the size of the database, and "at rest" their database (frontend) is about 100MB. And, they have several hundred clients (each with on average about 10 users), so the potential for problems is huge.

The problem was bumped up the line at Microsoft. But we finally got official confirmation that it is being fixed. In fact, we got a preliminary version to test. We don't know yet if this will be released as a Hot Fix or as part of next Service Pack.
Go to the top of the page
 
+
bnelson333
post Nov 8 2008, 10:13 AM
Post #16

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



Cool. We'll probably be migrated to SQL by the time this comes around, but it should help the countless other developers stop pulling their hair out over it.

Though if Microsoft knew what was causing the problem, it'd be nice if they told us in a KB article (e.g. if it was a linked table or something) so we could make our own workaround in the interim.
Go to the top of the page
 
+
MarkLiquorman
post Nov 8 2008, 10:23 AM
Post #17

UtterAccess Guru
Posts: 578
From: Florida, USA



I also wish there had be a KB article.

Unfortunately, we don't the know the actual cause or even the circumstances that would lead to the bloating. I don't think it was linking, because I could get a table with no links (and no objects of any kind) to bloat.

So, if you are still using Access as a frontend, even with SQL server BE, you might still have a problem.
Go to the top of the page
 
+
bnelson333
post Nov 8 2008, 01:22 PM
Post #18

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



In the troubleshooting I did, I was able to determine that the bloating got worse as more and more people had the db open (one single shared db). I needed to have 3 or 4 people help me test it, because just having myself in the db wouldn't cause it to bloat nearly as fast.

If we do a distributed front-end when we're on SQL, I'm not worried about the bloating. It will happen very slowly. And by being on the user's computer, they don't have to download it from a network share, so they won't see the speed problems we have right now. If all else fails, I can set the FE to compact on close and don't have to worry about complications because they will be the only possible user of their local front-end.

So why not split it today and do the same thing? We had huge speed problems when we tried. Yet another bug I couldn't figure out, but the split FE/BE (still both Access files) was 2x slower than a singular db. You'd think a split db would be at least a little faster because they don't have to download the FE if it's on their computer, not the case this time.
Go to the top of the page
 
+
MarkLiquorman
post Nov 8 2008, 01:41 PM
Post #19

UtterAccess Guru
Posts: 578
From: Florida, USA



>>If we do a distributed front-end when we're on SQL, I'm not worried about the bloating. It will happen very slowly<<

Don't bet on it. A test on our 100MB database produced bloating at a rate of 256K every 5 seconds.

We already use a split database schema, with each user having their own copy of the FE on their local machine. So that bloating was in the FE that was local to the user and not shared by anyone else.

My point about my opinion that it wasn't related to linking was because I could get a database with NO objects at all in it (no tables, no queries, no reports, no modules) to bloat at a rate of 4K every 5 seconds.
Go to the top of the page
 
+
bnelson333
post Nov 8 2008, 02:02 PM
Post #20

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



Yeah, the comment about linked table was just an example, e.g. Microsoft says "it's caused by linked tables" or "it's caused by x event in a form that does y". I'm sure it isn't a linked table, as I consolidated all my tables into one db when I was trying to fix mine and it still did it.

I too saw bloating happening in a new db with almost nothing in it. No code, a few queries, maybe 2 forms.

We'll just have to see how fast the distributed FE bloats. Even 100 MB wouldn't bother me if it's local on their computer, they shouldn't notice a big performance hit. The reason it's a problem now is because they have to download it from the network share to use it, so 20 -> 100 MB is a big jump, especially if they're trying to do it over WAN.

I've been thinking that rather than set it to compact on close, I might build something into the FE that if it reaches a certain size, it will just download a new FE from the central location. Completely compacted, just the FE should only weigh in at about 2-3 MB so they'd barely notice it.
Go to the top of the page
 
+

2 Pages V   1 2 >
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 21st April 2014 - 02:08 AM