SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
03-01-2008, 02:25 PM
| ||||
| ||||
 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.      |
|
03-01-2008, 02:26 PM
| |||
| |||
| 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/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
|
03-01-2008, 02:26 PM
| |||
| |||
| 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. |
|
03-01-2008, 02:26 PM
| |||
| |||
| 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/SQLSer...dbackID=286298 -- Erland Sommarskog, SQL Server MVP, esquel@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 |
|
03-01-2008, 02:26 PM
| |||
| |||
| Re: SSMS - getting timeout terminated even with Execution time-out of 0? On Jul 5, 5:56 pm, Erland Sommarskog <esq...@sommarskog.se> 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? |
|
03-01-2008, 02:26 PM
| ||||
| ||||
| 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/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
Linear Mode
