SSMS - getting timeout terminated even with Execution time-out of 0?

[M Bourgon]

On SSMS 9.00.3042.00, any query that runs longer than 10 minutes gets
the following error message:

The statement has been terminated.
Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.


I have changed the setting for Execution time-out to 0 in Tools-

Options->Query Execution, but it's still happening - in new windows

and after having restarted SSMS.

Any suggestions? I'm using QA to get around this for now, but this
seems a pretty basic issue.
Thanks.

[Mohit K. Gupta]

Go to the server in question, properties, Connections, remote timeout. By
default it's 600 seconds (10 min). To adjust that; but just curious, what
kind of query is it that it takes 10 min to complete?
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005

Post by M Bourgon
On SSMS 9.00.3042.00, any query that runs longer than 10 minutes gets
The statement has been terminated.
Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
I have changed the setting for Execution time-out to 0 in Tools-

Options->Query Execution, but it's still happening - in new windows

and after having restarted SSMS.
Any suggestions? I'm using QA to get around this for now, but this
seems a pretty basic issue.
Thanks.

[Erland Sommarskog]

Post by M Bourgon
On SSMS 9.00.3042.00, any query that runs longer than 10 minutes gets
The statement has been terminated.
Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
I have changed the setting for Execution time-out to 0 in Tools->
Options->Query Execution, but it's still happening - in new windows
and after having restarted SSMS.
Any suggestions? I'm using QA to get around this for now, but this
seems a pretty basic issue.

Hm, what happens if you run:

WAITFOR DELAY '00:11:00'

do you get the timeout error?

I don't, and I did not expect too. However, in order to verify that theh
error message really comes from SSMS, I changed the timeout setting in
SSMS to 30 seconds, and ran a WAITFOR for one minute. Which completed
successfully. And just like you I opened a new query window and even
restarted SSMS. I have tried re-booting my machine, though.

Maybe there is some weired bug that makes it difficult to change the
query timeout. But I would like you to try the WAITFOR statement, so
that I know that I have a good test case.

[Aaron Bertrand [SQL Server MVP]]

I think Mohit may be onto something here. Erland, are you trying on the
local server connection, or a remote query?

Post by Erland Sommarskog
WAITFOR DELAY '00:11:00'
do you get the timeout error?
I don't, and I did not expect too. However, in order to verify that theh
error message really comes from SSMS, I changed the timeout setting in
SSMS to 30 seconds, and ran a WAITFOR for one minute. Which completed
successfully. And just like you I opened a new query window and even
restarted SSMS. I have tried re-booting my machine, though.
Maybe there is some weired bug that makes it difficult to change the
query timeout. But I would like you to try the WAITFOR statement, so
that I know that I have a good test case.

[Erland Sommarskog]

Post by Aaron Bertrand [SQL Server MVP]
I think Mohit may be onto something here. Erland, are you trying on the
local server connection, or a remote query?

Bingo! When I connect to a remote server and run the WAITFOR, I get the
timeout error. And if I then switch connection back to the local server,
I now get the timeout error there as well. I can open new windows to the
local server, I can stop and restart SSMS again, and the timeout error
keeps on coming for the local server.

An absolutely baffling bug!

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286298

[M Bourgon]

Post by Erland Sommarskog

Post by M Bourgon
I have changed the setting for Execution time-out to 0 in Tools->
Options->Query Execution, but it's still happening - in new windows
and after having restarted SSMS.

WAITFOR DELAY '00:11:00'
do you get the timeout error?

I do.
Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.

Looking at what Mohit posted, my server does have the remote query
timeout of 600 seconds. But this is the first time I've ever seen
this issue. Odd.

Okay - this is odd - if I create a new Database Engine Query, it works
properly, even though (with the same instance of SSMS running) I
managed to have it timeout on me with the WAITFOR test.

So, is that why Query Analyzer doesn't get killed after 10 minutes?
Because it's a local connection?
Or did you just manage to create a bug separate from what I was
seeing?

[Erland Sommarskog]

Post by M Bourgon
I do.
Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Looking at what Mohit posted, my server does have the remote query
timeout of 600 seconds. But this is the first time I've ever seen
this issue. Odd.
Okay - this is odd - if I create a new Database Engine Query, it works
properly, even though (with the same instance of SSMS running) I
managed to have it timeout on me with the WAITFOR test.
So, is that why Query Analyzer doesn't get killed after 10 minutes?
Because it's a local connection?
Or did you just manage to create a bug separate from what I was
seeing?

I don't think the configuration option that Mohit mentioned has anything
to do with it. Since I know that it has the default of 10 minutes, it
was the first thing that came into my mind, but two things led me to
believe that it was not involved: 1) you did not get the error in QA.
2) The negative message number -2 is likely to be generated by Mgmt Studio
itself.

But apparently Mohit's question was useful, as it inspired Aaron to ask
the right question.

[Mohit K. Gupta]

Glad to help ;-). This newsgroup is being a huge learning experience for me.
I am able to test out my understanding and see what other people are doing.
At our work I don't get to do alot of stuff because of the business model we
are stuck in. Thanks guys ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005

Post by Erland Sommarskog

Post by M Bourgon
I do.
Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Looking at what Mohit posted, my server does have the remote query
timeout of 600 seconds. But this is the first time I've ever seen
this issue. Odd.
Okay - this is odd - if I create a new Database Engine Query, it works
properly, even though (with the same instance of SSMS running) I
managed to have it timeout on me with the WAITFOR test.
So, is that why Query Analyzer doesn't get killed after 10 minutes?
Because it's a local connection?
Or did you just manage to create a bug separate from what I was
seeing?

I don't think the configuration option that Mohit mentioned has anything
to do with it. Since I know that it has the default of 10 minutes, it
was the first thing that came into my mind, but two things led me to
believe that it was not involved: 1) you did not get the error in QA.
2) The negative message number -2 is likely to be generated by Mgmt Studio
itself.
But apparently Mohit's question was useful, as it inspired Aaron to ask
the right question.
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx