SQL Timeouts and Machine config

Posted on 2006-05-19
Last Modified: 2012-08-13
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.
Question by:rocketdoctor
    LVL 7

    Expert Comment

    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?
    LVL 25

    Accepted Solution

    <%server.scripttimeout = 90 'seconds%>

    also, you can set it in IIS, and SQL Server
    LVL 2

    Expert Comment

    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.
    LVL 2

    Expert Comment

    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.
    LVL 75

    Assisted Solution

    by:Anthony Perkins
    >>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:
    LVL 7

    Expert Comment

    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.


    Author Comment

    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.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Text search on single website page 16 42
    How to solve this equation 3 43
    SP to delete duplicates 15 47
    Import csv files to MS SQL 5 37
    Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
    This demonstration started out as a follow up to some recently posted questions on the subject of logging in: and…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now