I'm a web developer which currently has the joy of managing a client website that is still using Classic ASP that uses SQL Server 2000.

The code is quite old and in the process of been updated to a completely new system but in the interim we have to keep the websites running as smooth as possible.

There are three sites, a commercial front end, UK backend and Singapore back end. The two backends have a nearly 95% identical code base and each has a database on the same server and are hosted on the same machine.

MY issue is that for the last two or three days there have been errors for users when they try to log in on the Singapore website, the error is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e31' 
[Microsoft][ODBC SQL Server Driver]Timeout expired 

The code in question is a simple UPDATE statement, updating a session variable for one entry and the code has been running fine for many years.

There have been no recent code changes either that would affect this. How can I diagnose what is causing the bottleneck, EventViewer, SQL Error logs and IIS logs seem to not show errors around the time the timeout occurred.

My guess that the error is occurring when this code tries to run the UPDATE but there was/is an earlier process choking the SQL Server, but not been a DBA I am only guessing at this point.

Many thanks John

closed as too broad by Mark Storey-Smith, Paul White, RLF, RolandoMySQLDBA, Max Vernon Aug 6 '14 at 23:04

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    have you checked the stats and Index fragmentation on the tables involved in the UPDATE statement. Can you post the screenshot of the frontend error message and is it reproducible in a test environment ? A sql profiler trace would help you out. – Kin Nov 28 '13 at 20:42
  • You should investigate the points that Kin mentioned. I would also suggest the obvious step of double-checking the schemas of the two databases to ensure that nothing changed. In addition, as a workaround, you could change the timeout period for the ODBC driver used by your website. I assume that you are using the standard 30 second timeout. Try raising it to 60 seconds or set it to 0 (never timeout) if that is not too extreme for you. – RLF Nov 29 '13 at 15:53
  • Further investigation of the IIS logs shows someone trying to access the site at least once a minute, sometimes twice for over two hours in the early hours, from two differing IP addresses. My guess is that they were trying to access the site using a script and this had a knock on effect somewhere. Still investigating and will take Kin's advice and RLFs into account later this week. Manythanks. – John Cogan Dec 1 '13 at 11:57

I struglled with this issue for one week.

A the beginning, I started with a 45 seconds query for which the timeout error message was relevant. I reduced the execution time to 3 seconds and had still the error.

While seraching the Internet, I found out many cases telling the message itself is not relevant, and for this reason, my further investigations were made in other directions. I tried many workarounds I found in many forums without success.

Within my ASP code, I'm using a ADODB.Command object. This object has a CommandTimeout property expected to have a 30 seconds value. I tried to setup the value to 0, which means an unlimited timeout and I had no more the 80040e31 error. Then I tried a 30 value, and again, it worked.

For some reasons, it looks like the defalut value for the CommandTimeout of the ADODB.Command moved to another value. Setting up this value back to the expected default value solves the issue.

Hope it will work for you too.


Not the answer you're looking for? Browse other questions tagged or ask your own question.