Access 2000 MDB Memory Issues

[devilman0]

I have an access 2000 db that is read from a number of workstations through a share (varies from 2kpro sp4 latest to xp sp2 latest) from a file server (windows 2k adv server, sp4, latest) over a 100mb to 1000mb network (server and 3 other machines are 1000mb.) Database size is about 190mb, growing about .5mb a day. Clients range from 256mb celeron 900mhz to pent 4 3ghz HT. (all are dell.) The database in question is located on the server in an external firewire 160gb drive. It has been located here for 3 weeks with out issue. Recently (yesterday 11/11/2004) this little gremlin arouse. What happnes is, when i open any application that uses this db, i watch the mem usage, and it grows to db size + normal mem usage... on average up to 250mb for that app. After this happens 2 or 3 times the application loads, and uses normal amounts of mem (~20mb).

This first lead me to believe that this was a problem with the db file itself. I then tried opening this with access, so I go to the folder that the file is located, click on it to open it, now explorer is doing the same thing. I now think, AV. I disable AV (AVG 6, latest) and try again. Loads the same way, takes up gobs of mem, 2~3 times then becomes responsive again. If I go to the command prompt, and use regular ole' copy, and a file with the same name already exists on local machine, cmd will take up gobs of memory as well, i hit Y for overwrite... Copy does it's thing (also no more memory used during copy.)

I try to open this file on the local hard drive, explorer acts as normal, access only uses ~ 10mb max when opening.
This leads me to believe the network.

I have archives of emails in zips ranging from small (60mb) to large (808mb.) I can click on one of these in explorer, and it doesn't cause more mem usage, I can open this with IzArc and it uses the average amout of memory (~20mb) and i can extract to the local drive (again, no more than usual amounts of memory.) This zip file is located on a diff. drive than the access mdb. So i copy the zip to the external, and try using the same process (click, double click, extract.) And i get the same thing as if it were on the other drive (no extra mem usage than normal.) Now, this leads me to believe there is something that windows is doing with this MDB, but I can't find any solution on microsoft's website nor here or google. I have run a full AV and spyware scan (sb s&d and adaware.)

This one has me sumped, and with 15 users accessing this database 2X on each machine, load time and network performace is in down the drain.

Thanks in advance for any clues.

Thanks,
James

"Make it idiot-proof and someone will make a better idiot." ~bumper sticker

 

[bboffin]

While I have no specific explanation for the behaviour you are seeing you should know that 15 users is probably too many for an Access 2000 database. The generally accepted limit is around 8 to 10 with 5 to 8 being the limit for any real degree of reliability.

To resolve this problem Microsoft introduced MSDE (there will be a copy of it on your Office CDs) which is a cutdown version of SQL Server and avoids most of the problems that afflict Access as the number of users rises.

I realise that this is small comfort to you but there is a way you can adapt your Access applications to use MSDE without needing a major rewrite.

Access includes an 'upsizing' wizard that will recreate your database tables and all the data in an MSDE database.

You then access all the tables via linked tables using an ODBC connection. In most cases this is the only change needed to the application provided that the new linked tables are given the same names as the original tables. All your queries, forms, reports etc should still work (there will be the occasional exception that will need tweaking)

You administer the central database using Access 2000 but you create a project file (.adp) not a database file (.mdb) to do this. This project allows you to design the tables and carry out other admin tasks on the actual data.

Having done this you will generally find a huge improvement in performance but you may start to experience ODBC errors that appear to lock up the system. These are generally caused by Access forms that allow direct editing of the data displayed being left open for too long with modified data uncommitted. SQL Server has a habit of increasing the scope of any locks if they remain unresolved for too long so a page lock may escalate to a table lock or even a database lock and this gives rise to the ODBC errors. If you have a full copy of SQL Server there is a tool that can identify the cause of the lock but this isn't included with MSDE. The basic solution is generally to close down applications until the lock gets released. In the longer term it is better to move away from this type of processing and perform all data updates using SQL Stored Procedures which are queries that are stored in the database itself and are administered using the Access 2000 project mentioned earlier.

This is a lot to take in but if the application you are using has a reasonably long life expectancy and if more users are anticipated the effort to move to a better back end database will be well worthwhile.



Bob Boffin

 

[devilman0]

I hear you about the performace issues etc with access on many clients, unfortunately, the software we use is 3rd party propritary, and absolutly does not use any real SQL or otherwise server engine. (The software is called Alpine View, and its used in designing roof and floor trusses.) This software is the worst when it comes to performance and stabliity (many often database "corruptions" occur.) On another note, would 15 clients, possably locking the file cause this behavior (not the database structure, but actually the file)? Still seaking a way to reverse enginer this thing to use a real database system.... ;-)

Thanks,
James

"Make it idiot-proof and someone will make a better idiot." ~bumper sticker

 

[devilman0]

While i know all about all what you just described, unfortunately, this db is used by our engineering software. They are unwilling to move on to a real database server. So right now i'm stuck whith this (looking at some better software from a different vendor.) Thanks for your post.

Thanks,
James

"Make it idiot-proof and someone will make a better idiot." ~bumper sticker

 

[bboffin]

I'm sorry to hear that it isn't in your power to fix the underlying problem. The other symptoms you report all point to the same cause, too many users trying to use the same Access database. Believe me I've been there too and the least cost solution is to move onto SQL Server or MSDE while retaining Access as the interface.

Do all your users have to have access to the same master database? Would it possible to split the users into smaller groups or even individuals in order to reduce the number sharing the same database? If necessary you could give some users access to all of the different databases using different shortcuts. Is there a way of exporting designs from one database and importing them into another?

These are just ideas that may help you to work through your problems until you can find a better solution.

Good luck and let me know if you need any more help in resolving this matter.


Bob Boffin

 

[devilman0]

I can't even do that. This software is so bad... You take the stability of win 95 and halve that, add on sorry support, and a licensing system that is incompatable with NT anything (nt4, 2k, xp) and you just described the software. They don't and according to the devs., will never have a real server based SQL engine. I can only hope for new software Thanks for you help so far.

Thanks,
James

"Make it idiot-proof and someone will make a better idiot." ~bumper sticker

 

[bcastner]

Check at least for this:

http://support.microsoft.com/?kbid=315237

 

[koala15]

A shot in the dark:

You said "...it grows to db size + normal mem usage", since you have multiple users, I wonder what DB locking is being specified, as it sounds like the app is making a local (in memory) copy of the DB.

Greg.

"Life is full of learning, and then there is wisdom"

 

[devilman0]

Really, it doesn't seem to be a db issue at heart. Because what ever app touches that file, be it explorer, copy, xcopy, it seems for some reason either windows or other copies the entire file to memory. (When on the file server.)
I'm going to try the TCP/IP setting suggested by bcastner.

Also to give a bit more background, the server is on 1000mb, 3 other clients are also on gb, the rest are on 100mb switches (Full duplex) Changing speed/duplex of all does not effect this issue.

Thanks,
James

"Make it idiot-proof and someone will make a better idiot." ~bumper sticker

 

[devilman0]

Changed tcp size bigger/same/smaller than what's on ms's site, and the issue only got faster/slower/slowest. I'm starting to think this may be due to ole or something, but it's odd that it just started all of a sudden. And trying this from other machines, with a crossover cable etc doesn't help the solution at all. Windows (or someother componet in windows) is forcing this stuff to memory for reasons I can't find any reasoning for. Thanks for your posts so far.

Thanks,
James

"Make it idiot-proof and someone will make a better idiot." ~bumper sticker

 

[koala15]

James,

Have you tried to "Repair/Compact" the DB?

Greg.

"Life is full of learning, and then there is wisdom"

 

[devilman0]

Yup, tried that several times. I ended up disabling caching of offline objects and the problem went away. Odd that it was caching MDB files, even tho, all the m$ white papers say "No", but disabling that seems to have done the trick. Thanks for all of your posts.

Thanks,
James

"Make it idiot-proof and someone will make a better idiot." ~bumper sticker