Link to home
Start Free TrialLog in
Avatar of mko0
mko0Flag for Denmark

asked on

ASP adodb function sometimes timeout

On our w2000 iis5 web server sometimes simple adodb functions timeout.
Normalt it finish in less than a second - but often it timeout.

There is no CPU load and lots of free ram. (approx. 700 of 2000 mb used)
The CPU is a Intel core 2 duo.
I see not difference in the load when it works and when it does not.
In windows temp a lot of zero byte jetXXX.tmp files are created.

Also the server have run normaly for month, the problem startede around 2nd july.
(This is where I start see timeout errors in the web log).
Nothing have changed on the server or web files (except windows updates).

I want clues to locate the source of the problem or simply a solution to the problem...

SOLUTION
Avatar of _Stilgar_
_Stilgar_
Flag of Israel image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mko0

ASKER


Simple is: open and readnext while not eof.

Connection is jet 4.0:
  dcnDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                            & "Data Source=" & db &";"_
                            & "Jet OLEDB:Database Password=;"


Avatar of mko0

ASKER


I have tried to run the web site that cause the problem isolated.
Then added an other web site that run pooled.

When I run the simple adodb on the new site it always work.
If I run the simple first (after restart) on the problem site it also seems to work, until first time the actual site is visit. But still from time to time it does run fine. But it seems to indicate that it might be some other code on the site that lockup jet for this process. I will try to run a compact+repair on the mdb files.

The site uses 4 mdb's one 33 MB, one 96 MB, one 44 MB and one 3,5 MB.
I currently develop on Access 2k7 and Access 2k3.  They both have something called OLE/DDE timeout.  I think by default it's set to 30 seconds.  You might try to extend that to 45 secs or 60 secs to see if that helps.  However, this won't fix the problem.  It is just a possible way to accomodate longer fetch times for your larger databases.  When a database table is open, is it set to return ALL records or is the code filtering and reducing the recordset size with a query?  Can you identify which database has the problem?  Also, make sure all of your OS and Access updates are done, particularly MDAC(data access) libraries.  You can see what revisions you have under Tools/References in the VBA editor.
Avatar of mko0

ASKER

I have tried above solutions with no luck.
For some reason the problem however now seems less frequent.

I will try to identify if one DB cause the problem. - by disabling the dbs one by one and see if that clears the problem.


Did it become less frequent after you extended the OLE/DDE timeout?   I think the issue is with the size of the the working recordsets.  A few of the databases are getting pretty large for Jet DBs.  Expecially if they are active and busy.  It may be time to consider upgrading to SQL 2k5 Express or even MySQL for the future.  Maybe we can still figure out what is going on right now.

Has anyone looked at the server hardware?  How are the server's resources being used?  You might want to look at Task Manager and see what service is using the most CPU time and memory.  The problem could be an issue with server memory or even hard drive controller.  As databases grow it's important to track server resources and make upgrades when necessary.
Avatar of mko0

ASKER

About load see original question above:

There is no CPU load and lots of free ram. (approx. 700 of 2000 mb used)
The CPU is a Intel core 2 duo.
I see not difference in the load when it works and when it does not.

I did not extend the timeout as I do not consider that a solution.
Normaly the query take less than a second, so more than 30 seconds timeout will not help.
How ever I do belive it might be worth trying to extend to see if the query ever will complete.



That's what I was thinking when I mentioned that it won't fix the problem but it might help until you find what the problem is.
Avatar of mko0

ASKER

I don't think we get any closer so I will close the question for now.
Thanks for the clues.
Avatar of mko0

ASKER

The solutions did not solve my problem but as I requested clues to help finding a solution I have accepted the execellent clues submitted by the experts.