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

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.

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

M Bourgon (bourgon@gmail.com) writes:
> 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.

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.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

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

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






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

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

Aaron Bertrand [SQL Server MVP] (ten.xoc@dnartreb.noraa) writes:
> 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/ViewFeedbac k.aspx?FeedbackID=286298


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

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

On Jul 5, 5:56 pm, Erland Sommarskog wrote:

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

> Hm, what happens if you run:
>
> 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?

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

M Bourgon (bourgon@gmail.com) writes:
> 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.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx