• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1475
  • Last Modified:

SQL Timeouts and Machine config

I have a slow database and my server keeps timing out on the SQL Connections after 30 seconds.   I want to increase this time to 90 seconds.

 I have increased the connect timeout to 90 seconds in the web config

      <add key="ConnectionString.Databasel" value="Initial Catalog=db;Data Source=localhost;User ID=test;password=test;Connect Timeout=90" />

and modified to the machin.config to be 90 seconds

after each setting Ive reset the server but still get the timeout after 30 seconds.  any idea how to increase this.
2 Solutions
The problem is more then likely with the sql statement you are executing.

What kind of DB is it? access, msSQL, mySQL ?

If you run the SQL statement directly in the database how long does it take?

Can you show the statement here?

BTW: This looks like its a script timeout not a db timeout.  


How many rows are returned by your statement?
<%server.scripttimeout = 90 'seconds%>

also, you can set it in IIS, and SQL Server
Do you have good indexes setup? I only ask because you didn't mention that specifically. It's also the #1 thing for improving the performance of queries for our site.
Are you using stored procedures?

Also, try putting one of the worst queries into Query Analyzer and click Ctrl-L. What are the steps it says it takes to process the query?

If you're timing out with a sql query, and the sql Server isn't under heavy load, putting it into a stored procedure more than likely won't help.
You need to optimize the query, or limit the number of rows being returned. Put that query the the optimizer and see where the bottlenecks are.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

I've always been told that putting things in stored procedures will help with the server load issues to an extent but not on an individual query.
 I've been where you are regarding timeouts many times. Here are some more things that you might want to look at...

-Use "with(nolock)" in your select statements. For example "select from widgets with(nolock) where...".
-Only select the colums of information you need. If you can get it down to colums that are all contained in one index, it really improves things a lot.
-I don't know how exactly your connection is done but moving from cursors really helped us. For us, this meant changing our connections from "rsInfo.OPEN(sql, [connectionstring], 3, 3)" to "rsInfo.OPEN(sql, [connectionstring], 0, 1)".

Also if you could run one of the problem queries in QA and show the basics of the execution plan, I'm sure somebody can help you out a lot more. There are many different things that can cause timeouts and that's the first place to look when trying to figure out which on it is.
Anthony PerkinsCommented:
>>after each setting Ive reset the server but still get the timeout after 30 seconds.  <<
That would be because there is no support in the Connection String property for the Command Timeout (don't confuse this with the Connection Timeout).

>> any idea how to increase this.<<
You need to change the SQLCommand object's CommandTimeout property from the default 30 seconds.

You will also find experts more responsive, if you post in a more appropriate Topic Area such as:
Ghasano - Just to add to what you said.  Stored procs are precoompiled so they do execute faster. Also you reduce the traffic from the site to the DB when your not passing a whole big statement.

And one of the BIG benefits is that you reduce your risk of SQL injection attacks greatly.

acperkins -  Right, I got caught by that one once. There are three timeouts to consider command, connection, and script.

rocketdoctorAuthor Commented:
SQL Statements are fine, the database design is not and has scaled to big to handle even the best of queries.  It needs to work for only a few more months while we work on new designs therefore I just needed a bandaid.  The commandtimeout worked so giving credit there and to the scripttimeout.

FYI stored procedures are fast but they don't keep your code database agnostic so if you have a lot of stored procedures and switch databases down the road you have a lot of work.  Thats what we are doing and its a huge pain.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now