2

I have a client with an Access Database that has been experiencing slow queries in the past few days. Here is some background first.

  • it is currently running on Windows Server 2008
  • the mdb file is on the server and there are some local installed front end parts on the workstations.
  • we run over a gigabit ethernet and gigabit network switches
  • this server is used for basic file sharing too and those other services DO NOT seem to run slow or exhibit any issues.
  • this was working fine but has begun having slower queries or response times when you connect back to the database for queries.
  • we have not changed anything on the server or done any updates to either the server or the database front end installation.
  • we have always used a mapped drive letter to access it, both on the old server and on this new one, see next point for age of new server.
  • I know people will say stop using Access as a database but the client is using legacy software with NO path to upgrade and it was working, in a tolerable fashion, on this Server 2008 platform for about 1.5 years already.

My question is basically is there anything that I should be looking at that is maybe obvious but I might be missing? We tried moving the files to another partition on a faster raid array and we got the same results.

Any help would be greatly appreciated.

dasko
  • 1,244
  • 1
  • 22
  • 29
  • 2
    Have you compacted the database? – Zoredache Jul 12 '12 at 00:33
  • Zoredache, yes the client does a compact twice a day but i am not sure if it is command line based or not, i can check. – dasko Jul 12 '12 at 01:03
  • FINAL UPDATE FOR NOW: The client indicated that compacting it did run very well for the about 6hrs then when it hit 85mb it ran really slow. i explained to them that they should compact around 45mb, as per the vendors suggestion too, so we will leave it at that for now. thanks for the help. – dasko Jul 13 '12 at 18:56
  • 1
    Its curious that @ 85MB its choking. Oours would hit several hundred MB before it ran so slow that the users would start to complain. – MikeAWood Jul 13 '12 at 21:07
  • @MikeAWood, yes it is kind of weird but could it be the way the database was built? The vendor is really adamant about not letting it get larger than 50MB. – dasko Jul 14 '12 at 00:52
  • I would certainly point to the vendor and tell/ask them to fix it. Exporting the tables out to a SQL server and converting the data to linked tables might help, but that is a pretty big design change and not something that can just be done in a few minutes. – MikeAWood Jul 16 '12 at 19:16

1 Answers1

3

I second Zoredache's question about compacting the mdb file. When our apps were all Access based, i used to use Microsoft's Jet Database command line tool. It works great and keeps you from having to open and close the mdb in Access which could take a bit if it is already having issues. Check it out here. http://support.microsoft.com/kb/295334

A few other things: How busy is the server itself? (age of it means nothing if it is getting slammed by some other process Are all the copies of Access running on the same version? It's possible there is some glitch on one of the workstations creating locks for the other users Is the database corrupt? (the jet tool can help with that)

MikeAWood
  • 2,566
  • 1
  • 12
  • 13
  • MikeAWood thanks, the server 2008 unit does not exhibit any abnormal behavior or high usage while looking at both performance logs and resource monitor. There is only ONE Access database run on this system, at least i believe so but i can check with the client. As far as i know there is no evident corruption and the software vendor has verified for any locks etc in the tabls.It is also on a raid10 setup. The issue did seem to also be present when most users were gone for the day and i was testing with the client. – dasko Jul 12 '12 at 01:00
  • It is probably that the queries are simply growing the database as it is used. It doesn't sound like this is a hardware issue, but more of a poorly written software issue. One thing you could do is copy the mdb to another folder on the server and open it up and see if the 2nd copy has the same issues as the first. If not, you can probably eliminate the server as the bottleneck. Just make sure you are running on the compacted version. One stop gap measure we did was to link the tables all to SQL in Access and move the data out of the mdb. But it is a pretty involved process to do this. – MikeAWood Jul 13 '12 at 21:10