locked
SQL 2005 Management Studio Timeout expired RRS feed

  • Question

  • I get the message - "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding" - when working with large tables in Management Studio.

    I have tried changeing the following:

    1) Selected Tools -> Options

    2) Expanded the "Query Execution" node

    3) Clicked on "SQL Server"

    4) Set value for "Execution time-out"  to 0 and  various  numbers up to 1800

    Also checked the following

    1) In Object Explorer I right-clicked on the server and selected "Properties"

    2) Selected the "Advanced" page

    4) Set the value for "Query Wait" under "Parallelism"  to various values from the default of -1 up to 1800.

    I also stopped and restarted SQL after each change.

    None of the above changed the fact that the query stopped with the error mesage after about 30 seconds.



     


    Tuesday, April 18, 2006 8:44 PM

Answers

  • Dan,

    After hitting the same error, I stumbled upon the corrent setting.

    In the Management Studio, from the Tools menu, select Options, then click "Designers". There is an option called "Override connection string time-out value for table designer updates:"  In the "Transaction time-out after:" box, you will see the magic 30 seconds.

    Thursday, May 11, 2006 10:12 PM
  • So, I ran into this issue running Windows Server 2008 Standard Edition and SQL Server 2005 and 2008 Express. When I opened up Management Studios Express, The first remote connection to the server database would timeout, then I could connect the second time. I attempted to solve this issue by uninstalling server 2008 and installing server 2005 to no avail. I tried every trick listed above and none of them worked for me. I finally found the answer to my issue in the "Connect to Server" Window that appears when you first open Management Studios. Here's what you do:

    -In the "Connect to Server" window, make sure the server you are having trouble connecting to the first time is selected.

    -Click the Options button

    -This brings up the Connection Properties tab. Under this tab, select the option "browse server" in the "Connect to database" dropdown.

    -It will bring up a statement about connecting to the server. Click yes. It may time out on your the first time, and if it does click the "browse server" option again.

    -Once the server is brought up, select a database to connect to. (Don't worry, you'll still have access to other databases even if this is selected).

    -Under Network Protocol, select "Named Pipes"and then click connect.

    -It should connect the first time to the database. Close Management Studios.

    -Open Management Studios and "Connect to Server" window pops up again. if your remote database connection isn't the default connection, select it then click connect. It should connect the first time for now on.

    Hope this helps!

    Friday, July 9, 2010 2:56 PM
  • If you change the Execution time-out you need to open a new query over a new connection:

    File -> New -> Database Engine Query

    Instead of changing the execution time-out through Tools -> Options you should be able to specify it when opening the first query/connection to a SQL Server in the "Connect to Database Engine" dialog box:

    Options -> Execution time-out. 

    It works for me if I try changing it. 

     

     

    Tuesday, April 18, 2006 9:43 PM
  • Regedit:

     

    HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\DataProject\SQLQueryTimeout

     

    Default 30 value. Try 60 or 90 sek.

     


    I've been having this issue today as well. Running the SQL in a view works fine, but the "Open view" times out. The timeout for this .net connector is stored in the indicated registry entry. It's all separate from whatever timeouts one can set elsewhere in SSMS.

    However, changing that SQLQueryTimeout entry, then opening SSMS just wound up resetting this entry back to 30 seconds all the time. Only solution I found is to remove inherited permissions from the DataProject key, copying current permissions and then deny write permissions to the SYSTEM user. Once SYSTEM is denied write permissions the new value actually sticks.

    Tuesday, May 4, 2010 1:05 PM
  • Regedit:

     

    HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\DataProject\SQLQueryTimeout

     

    Default 30 value. Try 60 or 90 sek.

     


    I've been having this issue today as well. Running the SQL in a view works fine, but the "Open view" times out. The timeout for this .net connector is stored in the indicated registry entry. It's all separate from whatever timeouts one can set elsewhere in SSMS.

    However, changing that SQLQueryTimeout entry, then opening SSMS just wound up resetting this entry back to 30 seconds all the time. Only solution I found is to remove inherited permissions from the DataProject key, copying current permissions and then deny write permissions to the SYSTEM user. Once SYSTEM is denied write permissions the new value actually sticks.


    This worked out for me in view designer. Thanks!
    Monday, July 26, 2010 3:32 PM
  • For me, the above registry edit worked (HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\DataProject\SQLQueryTimeout setting it to 120), but like I said, it only worked on the actual server.  If I try to use a remote connection (with the same edit applied to the remote machine), it still times out after 30 seconds.
    Wednesday, January 13, 2010 8:18 PM
  • Perfect.  Thanks.  I was trying to update the data type in Design View over 24M records and this resolved it for me.
    Thursday, April 29, 2010 5:55 PM

All replies

  • What error info you saw in the  server errorlog?  Or check system event log to see what was going on with the server. Can you make normal connection, just use osql or management studio to connect to the database without any operation?

     

    Tuesday, April 18, 2006 9:08 PM
  • If you change the Execution time-out you need to open a new query over a new connection:

    File -> New -> Database Engine Query

    Instead of changing the execution time-out through Tools -> Options you should be able to specify it when opening the first query/connection to a SQL Server in the "Connect to Database Engine" dialog box:

    Options -> Execution time-out. 

    It works for me if I try changing it. 

     

     

    Tuesday, April 18, 2006 9:43 PM
  • The logs do not show any errors.

    I am using Server Management Studio to look at some tables and the large tables timeout before returning results.  I know that this was a problem in SQL 2000 using Enterprise Manager but was not a problem when you used SQL Query Analyzer.

    I have changed all of the timeout values to 1800 seconds for connections and queries and it still times out after 30 seconds.

    This is a new installation of SQL 2005 and I am new to SQL 2005, but I have used SQL 2000 & SQL 7 for many years.

     

    Thursday, April 20, 2006 2:29 PM
  • I changed both the connection time-out and execution time-out to 1800 and it did not solve the problem.

    II know that this was a problem in SQL 2000 using Enterprise Manager but was not a problem when you used SQL Query Analyzer.

     

     

    Thursday, April 20, 2006 2:33 PM
  • Dan,

    After hitting the same error, I stumbled upon the corrent setting.

    In the Management Studio, from the Tools menu, select Options, then click "Designers". There is an option called "Override connection string time-out value for table designer updates:"  In the "Transaction time-out after:" box, you will see the magic 30 seconds.

    Thursday, May 11, 2006 10:12 PM
  • I have now tried all these things above but still have problems with this. Enterprise manager OR Management Studio both gives Timeout Expired although Query Analyzer doesnt.

     

    HELP!!

     

    /Jonas

    Tuesday, May 23, 2006 8:46 AM
  • I am connecting to SQL Server 2000 from VisualStudio 2005 and am getting the same error...

    I have set all the recommended settings, tried all the suggestions, and have not been able to resolve the issue. 

    Any help would be appreciated.

     

    Friday, May 26, 2006 1:19 PM
  • Hi all,

    Had the same problem within a Web-Based Report <SQL-Timeout ....bla bla>

    Try setting your CommandTimeout settings within the connetion to 120 as:

    objConn.CommandTimeout = 120 

    objConn.Open

    That worked for me

    Uwe

     

     

     

     

     

    Wednesday, June 7, 2006 1:02 AM
  • Dan,

    Did you ever figure this one out? I'm working with rowsets of 40MM+ and expect to be moving to 200-500MM plus. This timeout has become a real problem. Have tried all of the suggestions in this thread to no avail.

    Chris

    Friday, June 9, 2006 1:38 PM
  • I would greatly appreciate if you could post a solution. I have the identical problem; none of the suggestions posted here have eliminated the 30 seconds limit so far.
    Friday, June 9, 2006 3:25 PM
  • I don't know if the following will work for everybody, but after much time struggling with the above issue, I stumbled across the following.

    There appears to be a difference as to where you execute your query. If you try to run a query from the "Query Designer" menu within the Management Studio, you will always get a timeout. The trick is to run a "Database Engine Query."

    To do this, click on the "Database Engine Query" in the Standard toolbar of the Management Studio (second button from the left). You will be asked to connect to a database engine. Before connecting, select "Options>>" from the "Connect to Database Engine" dialog box. Set the "Connection Timeout" in this dialog box to a large number (e.g. 1800). Then connect to the database.

    You can type straight SQL into the window that appears, or if you want a GUI, select "Design Query in Editor..." from the "Query" menu that now appears...

    Hope this helps some other people out there that have been butting heads against this problem.

    Thursday, June 22, 2006 12:32 PM
  • Excellent advice. My problem is, however, somewhat different. I am invoking a query as a gridview from a web page defined in Visual Web Developer 2005 Express Edition. How could I request a SQL engine query and specify the timeout limit from the gridview definition?
    Sunday, June 25, 2006 10:01 AM
  • Here's how I had to fix it.  In Tools -> Options then Designers -> Table and Database Designers I unchecked the Override connection string time-out value for table desinger.  This STILL didn't work... so I then turned the check on for it and up'd the transaction-timeout to 1600 seconds.  This fixed it which indicates to me that it's a bug with the Management Studio.  Hope this helps someone!  ;)
    Friday, September 15, 2006 5:06 PM
  • Hi,

     

    this tip solved the Problem with the management studio!

     

    thanks

    Tuesday, August 28, 2007 3:27 PM
  • This didn't solve the problem, which I think is the same as the others..

    I am connecting locally and also have suffered the same problme across a Remote Connection server.

     

    I get:

    ----

    SQL Execution Error

    Executed SQL statement DELETE FROM Merged

    Error Source: .Net SqlClient Data Provider

    Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    ----

    this occurs after ~30 seconds.

    I have tried the Override connection string timeout option but that doesn't work - any more suggestions?

     

    I have SP2 installed and a bunch of hotfixes.

    KD1

    Wednesday, September 26, 2007 9:28 AM
  • More info:

    This problem seemed to stem from when I used the SQL Query available after Opening the table to view data. If I used the 'full blown' (New Query) option and therefore open a new connection I don't get these timeouts. (Suggests an MS bug)

     

    OK, so that's fine for running ad-hoc queries but I've not tested for queries within code as a few of you are doing it seems...I'll have a go and find out soon.

     

    KD1

     

    Wednesday, September 26, 2007 11:00 AM
  • ----BPELL---------------

    Here's how I had to fix it.  In Tools -> Options then Designers -> Table and Database Designers I unchecked the Override connection string time-out value for table desinger.  This STILL didn't work... so I then turned the check on for it and up'd the transaction-timeout to 1600 seconds.  This fixed it which indicates to me that it's a bug with the Management Studio.  Hope this helps someone!  Wink

    ------------------

     

    WODERFULY solution, complete ... 4 minutes :-~

     

    Monday, November 19, 2007 2:32 AM
  • i'am get timeout expired , log file in databse SQL full.

     

    Friday, January 25, 2008 6:52 AM
  • Regedit:

     

    HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\DataProject\SQLQueryTimeout

     

    Default 30 value. Try 60 or 90 sek.

     

    Tuesday, January 29, 2008 1:59 PM
  • After having wrestled with this problem for a while, this last suggestion - editing the registry key - was the only way I've been able to prevent long-running queries from expiring when accessed by opening a view or by executing a view in the query designer. 

     

    Although those queries can easily be executed by running the same SELECT statement in a new query window, that defeats the purpose of having a query UI for less experienced users, such as a few of my clients that want occasional, limited access to the data.

     

    Has anyone come up with a better solution then editing the registry?  Does anyone know whether this issue is being addressed in a hotfix, service pack or SQL Server 2008?

     

     

     

     

     

    Thursday, February 21, 2008 2:54 PM
  • I was having the same issue. I may have a solution, but it gives me a head ache (error) with the verify query in query designer in sql server 2005. If you use the USE command and specify the database it seems to solve the problem. Below is an example of the query I was using.

    USE [database name]
    GO

    DECLARE @StartDate as varchar(10)
    SET @StartDate = '12/01/2007'
    DECLARE @EndDate as varchar(10)
    SET @EndDate = CONVERT(varchar(10), GETDATE(), 101)
    SELECT CONVERT(varchar(10), TestTable1.Datelogged, 101) AS LogInDate, TestTable2.id, TestTable2.Name, TestTable1.id2
    FROM TestTable2 INNER JOIN
    TestTable1 ON TestTable2.id = TestTable1.id2
    WHERE (TestTable1.Datelogged > @StartDate) AND (TestTable1.Datelogged < @EndDate)
    GROUP BY CONVERT(varchar(10), TestTable1.Datelogged, 101), TestTable2.id, TestTable2.Name, TestTable1.id2
    ORDER BY CONVERT(varchar(10), TestTable1.Datelogged, 101)




    Friday, August 8, 2008 6:00 PM
  • can't vouch for anyone else but this solution solved my problem right away.

     

    Tuesday, August 19, 2008 2:19 PM
  • Hi, everybody

    I have the same problem I'm running SSMS with MS SQL Server 2005. When I try to open view from SSMS interface, from local server (left mouse button on the viewàOpen view) it shows me “Executing Query” and after 30 seconds write such error:

    “Error Source: .Net SqlClient Data Provider.

    Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ”

    When I try execute only select from this view it tooks me about 1min 20 sec and its allright-I see the results.

    I resolved this problem locally when I changed in registry “SQLQueryTimeout” (HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\DataProject\SQLQueryTimeout) from 30 sec. to 600 on Server, but only locally now from SSMS I can run Open view and see the execution result. When I run this procedure from remote PC (client) SSMS I see the same problem

    (“Error Source: .Net SqlClient Data Provider. Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ”)

     

    Also when I try to run this view from remote PC from MS Access it shows me such error: “ODBC-call failed [Microsoft][ODBC SQL Server Driver]Timeout expired (#0)”

     

    I can’t find any solution.

    What shell I do? Any suggestions?

    Thursday, February 12, 2009 8:31 AM
  • I don't know if the following will work for everybody, but after much time struggling with the above issue, I stumbled across the following.

    There appears to be a difference as to where you execute your query. If you try to run a query from the "Query Designer" menu within the Management Studio, you will always get a timeout. The trick is to run a "Database Engine Query."

    To do this, click on the "Database Engine Query" in the Standard toolbar of the Management Studio (second button from the left). You will be asked to connect to a database engine. Before connecting, select "Options>>" from the "Connect to Database Engine" dialog box. Set the "Connection Timeout" in this dialog box to a large number (e.g. 1800). Then connect to the database.

    You can type straight SQL into the window that appears, or if you want a GUI, select "Design Query in Editor..." from the "Query" menu that now appears...

    Hope this helps some other people out there that have been butting heads against this problem.


    Using the Database Engine Query instead of new query or new view did the trick for us. Thanks for the tip.
    Thursday, November 19, 2009 8:21 PM
  • >Using the Database Engine Query instead of new query or new view did the trick for us.

    You can also set the Connection Timeout in SSMS Registered Servers properties.

    Let us know if setting it there resolves the issue with New Query timing out.


    Kalman Toth
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

    Friday, November 20, 2009 3:41 PM
    Answerer
  • Hi everyone,
    I had the same problem: the timeout error after 30 seconds...I've tried to change the registry key but nothing changed and I still get the error!Anyone has any more tip?I use Sql server management studio 2008, not 2005.
    Thanks!
    Friday, December 11, 2009 11:23 AM
  •  >You can also set the Connection Timeout in SSMS Registered Servers properties.

    How about the above?
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

    I've already set the connection time-out to 1800 seconds but nothing changed :( At first when I changed the registry key I was able to execute the query without the "where" statement but today I can't get the results in any case!I really can't figure out what is my problem!:(
    Sunday, December 13, 2009 12:37 PM
  • Okay, I have this same problem.  I fixed it through the registry edit on the server, but the same solution does not work if I'm using Management Studio with a remote connection.  Is there a way to fix this, or am I going to have to do all of my big table stuff directly on the server?
    • Proposed as answer by ITmaverick Friday, February 12, 2010 5:34 PM
    Wednesday, January 13, 2010 7:29 PM
  • For me, the above registry edit worked (HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\DataProject\SQLQueryTimeout setting it to 120), but like I said, it only worked on the actual server.  If I try to use a remote connection (with the same edit applied to the remote machine), it still times out after 30 seconds.
    Wednesday, January 13, 2010 8:18 PM
  • Perfect.  Thanks.  I was trying to update the data type in Design View over 24M records and this resolved it for me.
    Thursday, April 29, 2010 5:55 PM
  • Regedit:

     

    HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\DataProject\SQLQueryTimeout

     

    Default 30 value. Try 60 or 90 sek.

     


    I've been having this issue today as well. Running the SQL in a view works fine, but the "Open view" times out. The timeout for this .net connector is stored in the indicated registry entry. It's all separate from whatever timeouts one can set elsewhere in SSMS.

    However, changing that SQLQueryTimeout entry, then opening SSMS just wound up resetting this entry back to 30 seconds all the time. Only solution I found is to remove inherited permissions from the DataProject key, copying current permissions and then deny write permissions to the SYSTEM user. Once SYSTEM is denied write permissions the new value actually sticks.

    Tuesday, May 4, 2010 1:05 PM
  • So, I ran into this issue running Windows Server 2008 Standard Edition and SQL Server 2005 and 2008 Express. When I opened up Management Studios Express, The first remote connection to the server database would timeout, then I could connect the second time. I attempted to solve this issue by uninstalling server 2008 and installing server 2005 to no avail. I tried every trick listed above and none of them worked for me. I finally found the answer to my issue in the "Connect to Server" Window that appears when you first open Management Studios. Here's what you do:

    -In the "Connect to Server" window, make sure the server you are having trouble connecting to the first time is selected.

    -Click the Options button

    -This brings up the Connection Properties tab. Under this tab, select the option "browse server" in the "Connect to database" dropdown.

    -It will bring up a statement about connecting to the server. Click yes. It may time out on your the first time, and if it does click the "browse server" option again.

    -Once the server is brought up, select a database to connect to. (Don't worry, you'll still have access to other databases even if this is selected).

    -Under Network Protocol, select "Named Pipes"and then click connect.

    -It should connect the first time to the database. Close Management Studios.

    -Open Management Studios and "Connect to Server" window pops up again. if your remote database connection isn't the default connection, select it then click connect. It should connect the first time for now on.

    Hope this helps!

    Friday, July 9, 2010 2:56 PM
  • Regedit:

     

    HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\DataProject\SQLQueryTimeout

     

    Default 30 value. Try 60 or 90 sek.

     


    I've been having this issue today as well. Running the SQL in a view works fine, but the "Open view" times out. The timeout for this .net connector is stored in the indicated registry entry. It's all separate from whatever timeouts one can set elsewhere in SSMS.

    However, changing that SQLQueryTimeout entry, then opening SSMS just wound up resetting this entry back to 30 seconds all the time. Only solution I found is to remove inherited permissions from the DataProject key, copying current permissions and then deny write permissions to the SYSTEM user. Once SYSTEM is denied write permissions the new value actually sticks.


    This worked out for me in view designer. Thanks!
    Monday, July 26, 2010 3:32 PM
  • Thanks. This solved it for me. None of the other posts did the trick.
    Thursday, October 14, 2010 9:12 PM
  • I had the same problem.  I fixed the problem by increasing the SqlQueryTimeout setting in the registry from 30 to 120 seconds.  Here is what I did:

    Run Regedit in administrator mode.

    drill down to the following registry value Hkey_current_user\software\microsoft sql server\100\tools\shell\dataproject\sqlquerytimeout

    Change the decimal value from 30 to 120 (or what ever you desire).

    Hope this helps.

    Monday, November 21, 2011 10:28 PM
  • This certainly fixed the problem for me.  As I don't yet have SP1 installed the problem does not lay with SP1.

     

    Wednesday, January 4, 2012 5:21 AM
  • I don't know if the following will work for everybody, but after much time struggling with the above issue, I stumbled across the following.

    There appears to be a difference as to where you execute your query. If you try to run a query from the "Query Designer" menu within the Management Studio, you will always get a timeout. The trick is to run a "Database Engine Query."

    To do this, click on the "Database Engine Query" in the Standard toolbar of the Management Studio (second button from the left). You will be asked to connect to a database engine. Before connecting, select "Options>>" from the "Connect to Database Engine" dialog box. Set the "Connection Timeout" in this dialog box to a large number (e.g. 1800). Then connect to the database.

    You can type straight SQL into the window that appears, or if you want a GUI, select "Design Query in Editor..." from the "Query" menu that now appears...

    Hope this helps some other people out there that have been butting heads against this problem.


    Excellent! This one works fine...
    Friday, June 29, 2012 7:34 AM
  • Hi,

    Changing the registry key

    HKEY_USERS\S-1-5-21-648021851-1182688487-488402039-500\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\DataProject\SQLQueryTimeout

    did the trick for me. (It also changes the the same key in HKEY_CURRENT_USER)

    I ran into the 'Query timeout expired' calling a Stored Procedure from an external application running on the same server as SQL Server.

    Can anybody tell me what this key is for and if there's this setting can be found somewhere in SQL Server (or Windows Server)?


    • Edited by DeSuper Tuesday, August 28, 2012 11:57 AM clarity of my question
    Tuesday, August 28, 2012 11:57 AM
  • Changing the registry key

    HKEY_USERS\S-1-5-21-648021851-1182688487-488402039-500\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\DataProject\SQLQueryTimeout

    did the trick for me. (It also changes the the same key in HKEY_CURRENT_USER)

    How did you change it exactly? Thanks.


    Kalman Toth
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016


    Tuesday, October 2, 2012 5:57 AM
    Answerer
  • Regedit:

     

    HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\DataProject\SQLQueryTimeout

     

    Default 30 value. Try 60 or 90 sek.

     

    this is the true solution. it worked for me.

    thank you Jan348.

    but don't forget to close Microsoft SQL Server before editing SQLQueryTimeOut.

    any one have the same problem:

    1) Close Microsoft SQL Server

    2) Go to RegEdit : HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\DataProject\SQLQueryTimeout

    90 : M SQL Server 2005

    100 : M SQL Server 2008

    3) Set the value : 60 or 90   (the default is : 30 )

    4) start SQL Server and try 

    good luck ;)

    Thursday, April 4, 2013 11:59 PM