Linked Server - Timeout Expired
-
We have two SQL servers and a web site. The web site connects to one of the servers (let's call it WebSQL) and that server has a link to the second server (let's call it MainSQL).
On numerous occassions we have had web pages return an ODBC "Timeout Expired" error for a query on an ASP page. Our best guess at this time is that someone was on an ASP page that was taking too long to return a result, so they abandoned the page (e.g. stop, back, etc.) Once this happens, the same and other pages get this error. The only solution we have found so far to resolve this is to kill all the connections on MainSQL from WebSQL. Then, everything works again.
If we log into WebSQL in Query Analyzer with the same login/password as the web site and run the same query, it runs quick. But, still the web site is having a problem. The CPU utilization and memory on both servers is very low (< 5%).
What is causing this problem and is there any way to prevent it from happening?
-
Well ... handling these kind of problems is not easy .... that's one downside of working with linked servers.
Start sqlserver perfmon to captuer the load and analyse. (both servers !)
also start windows-perfmon to check what I/o and networktraffic is doing.
Are you suffering deadlocks ?
Keep in mind that linked server access is part of the timeframe for timeout of your website.
What kind of connection isolation level and/or query isolation level are you using (default may be repeatable read
) ?Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
No deadlocks, just lockouts. The isolation levels are most likely the defaults.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply