SQL 2005 Management Studio Timeout expired

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.

 

Answer 1

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?

 

Answer 2

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. 

 

 

Answer 3

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.

 

Answer 4

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.

 

 

Answer 5



Dan,

 

A couple of other possibilities worth checking.

 

In Object Explorer right click on the SQL Server 2005 instance select Properties. I am assuming that you may be attempting to connect remotely.

 

On the Connections tab check the value of Remote_query timeout.

 

On the Advanced tab check the value of Remote Login Timeout.

 

If neither of those fixes things ... Can you confirm whether you are connecting locally or remotely? Are you able to login to the server? Are you able to retrieve smaller quantities of data from the server instance of interest?

 

Andrew Watt [MVP]

<Dan [e-mail]> wrote in message news:[e-mail]...

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.

Answer 6

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.

Answer 7

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

Answer 8

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.

 

Answer 9

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

 

 

 

 

 

Answer 10

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

Answer 11

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.

Answer 12

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.

Answer 13

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?

Answer 14

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!  ;)

Answer 15

Hi,

 

this tip solved the Problem with the management studio!

 

thanks

Answer 16

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

Answer 17

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

 

Answer 18

----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! 

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

 

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

 

Answer 19

i'am get timeout expired , log file in databse SQL full.

 

Answer 20

Regedit:

 

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

 

Default 30 value. Try 60 or 90 sek.

 

Answer 21

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?

 

 

 

 

 

Answer 22

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)

Answer 23

can't vouch for anyone else but this solution solved my problem right away.

 

Answer 24

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?

Answer 25

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.

Answer 26

>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 SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 27

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!

Answer 28

 >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!:(

Answer 29

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?

Answer 30

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.

Answer 31

Perfect.  Thanks.  I was trying to update the data type in Design View over 24M records and this resolved it for me.

Answer 32

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.

Answer 33

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!

Answer 34

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!

Answer 35

Thanks. This solved it for me. None of the other posts did the trick.

Answer 36

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.

Answer 37

This certainly fixed the problem for me.  As I don't yet have SP1 installed the problem does not lay with SP1.

 

Answer 38

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

Answer 39

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)?

Answer 40

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 SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 41

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 ;)