OK. I am having this same problem. Mature application. FE Access 2007 pointed to BE Access 2007 tables. Normally the tables are 100 or so MB and the FE is 27MB. I can sit and watch as 5 sessions sit idle and the db FE jumps up about 10MB an hour. I can watch the jumps on a minutes by minute basis and I know these users are not doing anything. Again, the app was stable and there have been ZERO changes in the db by me (programmatically). Interestingly enough, I also have a timer event that clicks off every 20 secs to read a table in which a text has been stored fo rthat user to retreive. It is sort of a poor man's built in IM. If a user takes action on a Journal item that was assigned to him and the user assigning it has checked 'Response Required', then the issuing user gets a reply back saying that the receiver has taken said action (whatever the text is).
This has been working flawlessly for months with no expansion in size and the db has been growing in data size about 35-45MB a year for the last few years. Why all of a sudden this exponential growth?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Not sure...
Try the usual on the FE:
1. Run the compact/Repair utility
2. Create a blank new database and import all of the objects into it.
(then obviously re-link the tables to this new FE)
3. If you have autoupdates turned on, and MS Update, may be affecting this...
4. Have you done a full AntiVirus/Spyware/malware scan recently?
JeffCoachman
C&R takes less than 2 secs and FE db goes from 110MB to 23MB; where it should be. The tables go from 123MB to 118MB. That is reasonable for the activity. There are no queries or forms save a switchboard in the table BE db. All q's, forms, and code reside in the FE. The new FE after import was 50MB. After C&R, 25MB. The same size as the previous version after C&R. It took almost ten minutes to totally import but the navigation bar was set to display tables and related objects and that takes a long time anyway. No re-linking was needed, however. I used the new one for 5-7 minutes and the size grew by 2MB to 27MB. All I added was three estimates of single line, non-complex, image free data.
As for the Scans, we scan the entire machine every night. Auto-updates are on. Not sure what to do there except turn them off but the damage has been done, perhaps.
JDF
Well, I don't know that I would even be concerned with a 2mb increase in size.
An Access DB will always increase in size slightly as you use it.
That's just the cost of how the database works.
Every application "Leaks" to a certain extent.
In the same way that a Car standing still and idling will burn a little gas.
(Are you using any recordsets)
You can compact the FE on close because it is local to the user.
But again, a 2mb increase is hardly worth the trouble.
Besides, it will only increase by that same amount the next time anyway, so why bother?
You will probably never get it to *stay* at 23mb, and *NEVER* increase.
You can compact the FE every so often just to be sure.
But IMHO, I would just refresh it with a fresh copy of the "Reference" front end.
The BE must be compacted with everyone out of it.
Lets see what some other experts have to add...
;-)
JeffCoachman
I posted the original question referred to in this one. The problem there was that the BACKEND was growing (by a gig or more). What seemed to cure it was taking out the 'bump' routine, whereby a front-end form checked a field in a backend table every 10 sec. If it found a 'bump' instruction, it caused the front-end to close.
The general consensus seemed to be that the backend was somehow accumulating the status-checks, and with one every 10 seconds from a dozen or more open frontends, these would come to quite a number over the course of a working day.
I took out the bump routine and haven't had the problem since. Incidentally, this app was running SP2 throughout.
From all the comment on my original question, it sounds highly likely that your timer event will turn out to be the culprit.
Bellone
I think the comment I added regarding the 2MB growth in a minute or so is a distraction. The DB actually grows from 25MB in the morning to about 120MB by 5pm. I have seen growth before and I am familiar with the db processes that make this happen but I have never seen, especially in this db, growth like this in one day. The app is over 3 years old and migrated to Acc 2007 several months ago and has been working great with no huge growth that a compact and repair didn't take care of once every other week or so. All of a sudden, in the last week, it has been bloating by 100MB in one day! That worries me.
Bellone, you mentioned SP2. The app is developed in SP2 but the clients are all using Access RunTime 2007. Everyone has a shorcut to the same file on the server and they have RT installed locally. This has been a stable model throughout Acc 2003 and up. Not sure how to validate the RT version but I downloaded it from MS about 6 months ago.
Currently it is expanding at the rate of approx 1MB per minute. In the time it took me to type this it expanded from 27MB to 34MB = 5 users in.
JDF
NOTE: Some clients have had trouble maintaining connection to the server. The db will disonnect for no appearant reason. If this happened often enough, would the disconnect create this growth? Are the disconnects an effect of the growth problem. This may be a reach. The db is growing with solid connections, too, so that may be enough to dismiss this idea.
The disconnects seem to have been resolved but would they have left a scar in the db that it now has to deal with on a continual basis. Not sure where to look for this. I did save the imported db but I did not connect everyone to it this morning. I let them use the original one since I did not see any flags in the size of the newly imported db.
How about creating a spare front-end which doesn't have the timer event, and seeing whether it grows by itself, at the same rate as the other front-ends?
My terminal server user's front-ends all grow from 33Mb to around 90Mb every day. I am virtually certain this is due to a timer event, designed to cause a call-status form (displayed on SmartBoard) to be updated every 30 seconds. I get round this by having a batch file which overwrites their front-ends from the master version every night.
Bellone
I have opened the new FE on the server. Mind you , it is NOT running the RT but the full version of Access 2007. Either way, I expect the growth to be substantial enough that within a few minutes of use I would see some change. I made a new estimate (which is the heavist form) and viewed some forms with multiple queries on them and left it open as the users do. The timer runs on a form that opens after the user login as kind of a splash screen with action items on it. That is always open. In fifteen minutes the start size of 28,484KB is exactly the same as it is now. Importing all the objects into the new FE like boaq2000 (JeffCoachmen) suggested may have fixed it. I'm going to let it sit for several hours and then deploy it tomorrow if it doesn't grow. If that fixes it, then points to Jeff.
What if importing fixed the timer event? My timer event is requery code firing every 20sec on a table that may have been updated to include this particular users name and therefore any records belonging to them would be popped on the screen. I think it is fairly benign and has been in the program for almost a year now but I suppose its possible.
JDF Comments? I want to be fair.
Fair enough. Stay tuned. Some stats...
GT Import New - 1 User
======================
9:02 AM 8/20/2009 28,484KB
9:40 AM 8/20/2009 Same
9:41 AM 8/20/2009 Same
GlassTrak PRO - 5 User
=======================
9:22 AM 8/20/2009 36,388KB
9:40 AM 8/20/2009 38,832KB
9:41 AM 8/20/2009 39,092KB
9:42 AM 8/20/2009 39,656KB
Are these COMPILED FE's (i.e. .mde format)?
< Everyone has a shorcut to the same file on the server and they have RT installed locally.>
Do you mean that all connect to the same Backend? Or that they're all sharing the same Frontend (which would all share the same Backend)? If so, this is not the recommended method ... I know, I know it's been working for xxx months/days/years like that <g> ... but the simple truth is that's not the way to do it ... of course, if you are NOT doing it this way, and each user has a copy installed on their local workstation, then I remove my pithy comments <g>.
In Access 2007, these are C&R sizes for accdb. We are not using the MDE's (whatever they would be in 2007, I'm not sure.)
Re: Best Practices. I agree. By convenience only, the shortcuts at the client point to the FE (one file) on the server. The only thing installed at the client is the Acc 2007 RT client.
The reason it was deployed this way was because before I knew better I had 5 Term Servers serving 125 remote concurrent connections with load balancing plus 54 local domain clients. Each TS had 25 clients (max) all using a local copy of the FE. All 5 of the FE's pointed to a single BE (of course) which was a bunch of SQL tables. Each of the 54 internal clients were pointing to the same FE on an application server linked to the same BE (SQL). 6 years with upgrades, never had this issue. If it works don't fix it. I had a batch file replace the FE's when I made a change - 6 copies, one for each TS and one for the App Server.
So, I am dealing with an SBS2003 Server. No remote connections. All internal domain clients using the same copy of the FE. 3 years no issues.
By the way. The import of all objects into a new FE did the trick. No growth (a few MB's over 24 hours). Before the end of the day on 8/20 the db was 150MB. Today, 27MB - no crashes.
Boaq2000 on 8/19. I have to say your solution worked.
Thank all of you for contributing. That is the spirit of this site that is worth membership.
<All internal domain clients using the same copy of the FE. 3 years no issues.>
This is purely by chance <g> ... this sometimes works okay, depending on the nature of the application and the usage levels. In general, this is a recipe for troubles, and is most assuredly not in the overall "best practices".
I post this because EE is a knowledgebase, and those searching for solutions should understand that while the author's setup works for them, in the VAST majority of environments, the correct method for deploying a multiuser application is to provide a COPY of the frontend database to each user, and not to share that copy among users. This is recommended by every professional Access developer I know, and by Microsoft, and by EVERY Access MVP.
I am going to eat some humble pie here but for the sake of correctness:
I am sorry, Boaq. The db exploded to 160MB today starting from 28MB and now there are several machines getting "lost network connection" errors. I am beginning to think we may have an infrastructure problem. I think I wanted that solution so bad I didn't wait long enough for results. The growth appears almost immediately so I wasn't prepared for a day's wait before it happened.
Do you think the network errors may be the cause or the after effect? We are looking to see that the switch is not dying for some reason. This nasty disconnects; could they be creating excess bagage for the FE?
We will copy the FE to every machine as well to see if that helps as starters.
I will open a new question if moderator will allow it, other wise maybe we can re-open this one. I'd almost like to start over for clarity's sake. I pitty anyone trying to follow this thread.
Any suggestions would be appreciated.
Dropped network connects wreak havoc on Access databases, and are one of the leading causes of corruption and, of course, bloat. I'd fix those first (and deploy a copy to each user) before doing much else.
Also, if you do post a new question, use the "ask a related question" link (immediately above the box where you type in new comments) so that your new question will have a link back to this one.
Appreciate it. Thanks, Jim:
Experts: Update... I have copied all users with a local file of the FE. I found the server to be terribly fragged with less than 10% free space left and defragged the drive. It's a RAID-5 array w total space of 74GB. It's an older box and we do need to watch it until the new server comes on line in a few weeks. It now has over 30% free space. This can only help. The only reason I mention it is to edify.
I will be watching the day here to see if these two actions (defrag and individual FE's) helps the dropped connections to the BE on the server that we have been experiencing. It started on one machine and spread to three then four. It is coincidental with the size of the FE. As it grew during the day the connection dropping frequency increased. Not sure if connection drops begets the size of the FE as LSM pointed out (makes absolute sense) or vice-versa but needless to say the current configuration seems to be lacking somewhere.
Stay tuned....and thanks again for sticking it out.
That would make sense ... as the FEs corrupt, they would become larger, typically.
I'm betting you'll find freeing up that disc space will help quite a bit. Also, remember that if you have deployed individual FEs to each user, then the ONLY commonality between them is the backend, the machine hosting that backend, and the hardware required to get to that machine (at least the common routes).
Business Accounts
Answer for Membership
by: jdfullerPosted on 2009-08-19 at 16:48:16ID: 25138350
Refer to related Q: 24032220 - Link above "Related Solutions;"
Reading the whole thread will take a little time but my Q will make a whole lot more sense. :o)
jdf