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.

[Erland Sommarskog]

Aaron Bertrand [SQL Server MVP] (te*****@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/SQLSer...dbackID=286298
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[M Bourgon]

On Jul 5, 5:56 pm, Erland Sommarskog <[e-mail]:

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?

[Erland Sommarskog]

M Bourgon (bo*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx