April 23, 2003 at 3:41 pm
I am occasionally receiving a "Timeout expired" message on a site that is very, very fast and performs very well. I have run Profiler and not seen any deadlocks or error warning/messages, and also ran perfmon to look at memory and CPU on both the webserver and the database server without anything showing up. What else do I need to check?
April 24, 2003 at 5:52 am
It looks to me like ASP script time out and not database time out.
In any case the following might help
There can be two kinds of timeout in web application.
1. ASP Script timeout
2. Database connection time out.
If the error message in web page says just time out expired (without ODBC, or OLEDB)
then this means that it is asp script execution timeout error.
By deafult this is set to 90 seconds in IIS.
You can increase this one in IIS settings.
If the error message says (SQL*Server Driver: Timeout Expired) then it it is related to database. The default timeout is 30 seconds. This can be set when opening a ADO connection. You can increase the value by setting timeout property before executing open method
April 24, 2003 at 9:38 am
This is a database issue, the OLEDB provider returns this error - I've been programming for about 6 or 7 years so I know the diff btw a script timeout and a database timeout. What I need to know is if there is any way to figure out why the timeout is occuring since it happens intermittantly.
April 24, 2003 at 10:06 am
Have you tried enabling IIS log with extended log format and analying the log.
This might give some idea on pattern from which this error is getting generated.
April 24, 2003 at 10:14 am
You won't necessarily see deadlocks as they would come back to the client with the dealock error, but have you tried running sp_who2 to see if there is any blocking.
You may have a process that is locking a table, and the other process is timing out waiting for the lock to be released.
April 24, 2003 at 10:30 am
I don't see anything other than sleeping processes for that database. Would I have to do the sp_who2 right after the error occurs or would it stay in that state for a while? What's going on is that I have the system email me errors, but after I get the error email and go try it, everything works fine, pages come back in less than a second. I'm pretty sure this is some sort of multi-user scenario, I'm just unsure how to diagnose the problem. The IIS logging is difficult because the times that show up in it don't match up with actual server times for some reason, so I can't compare it to the time from the email I get. The site isn't terribly high traffic so I'm pretty sure it's some sort of concurrency issue on a certain table between maybe 2 or 3 users at the most.
April 24, 2003 at 1:22 pm
You can use microsoft provided blocker script within a .bat file. Create a job with the .bat file and schedule it to run every min. This proc gives quite some details on what was happening when the timeout(deadlock) occured.
April 24, 2003 at 3:29 pm
It seems to me this is sql login timeout.
When more than one user login at the same time, it may happen. The sql2k default time is 4 second. If you want to change, go to tools, option, advanced, then change the time you wanted.
Robert
April 25, 2003 at 2:28 pm
I am also having the same intermittent time out problem. I traced all the data. I made some observations....
1) some times the same query takes 2secs and also 32 secs. Whenever query takes more than 30 secs users are getting time out.
2) whenever it takes more than 30 sec, there is odbc or olddb warning or errors. One warning is sort warning. whenever i get users are getting time out.
some thing can i do with sort warning???
May be i fix the sort warning users will not get time out????
I need to fix this problem ASAP. Pl let me know..
April 25, 2003 at 3:22 pm
The 30 second timeout is the default command timeout - that you can change via ADO or .NET depending upon what you're using. Why it's taking so long sometimes and not others is what I'm trying to figure out. I've recently seen stored procedure take an error during a transaction and block all access to the table until the transaction timed out, so I'm looking into that and into concurrency problems with multiple users hitting the same table with transactions at the same time.
April 25, 2003 at 3:34 pm
In EM, try to click on tools, select option,
click advanced, then change query time_out time.
Robert
April 27, 2003 at 10:00 pm
I was having the same problem with ASP 3.0 and SQL2k. I tracked it down to a reporting program called Cognos Impromptu being open and locking certain tables. The queries Timed Out when the tables didn't unlock after the Timeout Period. Maybe you could look for something simlar.
April 28, 2003 at 11:10 am
We have also been experiencing 'Timeout Expired' messages on many of our DTS jobs. We discovered that if we truncated our transaction logs (Backing up the logs didn't seem to do the trick, so we had to restart SQL Server), the error messages would disappear.
April 28, 2003 at 11:30 am
Interesting.. We've actually scheduled nightly log truncations to keep the log file below the 10 MB limit.. It would get upward of 5 gigs inside of a week or two if we didn't do that and setting a size limit throws errors once it reaches that limit, so I think that was our only option. Since then though, I don't think I've seen the timeout problem recently.
April 28, 2003 at 12:13 pm
We were having similar problems with an application using ASP for a front end, VB6/COM+ in the middle and SQL Server at the back. We finally narrowed it down to MTS transactions. When we stopped setting the COM+ components to "Require Transactions" and started handling them in SP and ADO code we quit getting the timeouts (we were even getting transaction timeouts on simple select queries on tables that hadn't been added to in months). It's a little more work to code this way but the control you get makes it well worth it.
Dave Lech
Programmer's Law #1 - The Software is NEVER Finished.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply