| Author |
Topic |
|
R
Constraint Violating Yak Guru
328 Posts |
 Posted - 2011-10-24 : 10:18:39
|
| Within SQL Server 2008 SSMS (10.50.2500.0) all my queries time-out after 10 seconds. I've been into Tools > Options > Query Execution and set the Execution Time-out value to 60 seconds.However, all queries still time-out after 10 seconds. I've searched Google for advice but not found anything?If I right-click the server in Solution Explorer and go to Connections, the timeout value in there is set to 600 seconds.Has anyone got any advice on why this might happen please? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-24 : 11:12:22
|
| are you using a linked server query or cross db query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-10-24 : 12:18:51
|
| HiNo this is just a normal stored procedure running on a development machine with the database on that actual machine. Neither of the above are being used.Thanks. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-10-24 : 12:35:39
|
| Are you running it in the same window? Changing query execution settings requires either a restart of SSMS or opening a new query window in order to take effect. |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-10-25 : 02:42:50
|
| Hi. I did a restart and the problem still persists. The settings however have been remembered - so I really don't see how this is happening.I wondered if I should get 2008 SP3, however when I tried it complained that I had version 10.50 and it was expecting an earlier version. Not sure why that is as I've never installed SP3... I am rather confused by all this...! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-10-25 : 08:04:18
|
| Version 10.50 is SQL Server 2008 R2, SP3 only applies to SQL Server 2008. There's an SP1 for SQL Server 2008 R2 that you can apply: https://www.microsoft.com/download/en/details.aspx?id=26727 |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-10-25 : 08:34:55
|
quote:
Originally posted by robvolk
Version 10.50 is SQL Server 2008 R2, SP3 only applies to SQL Server 2008. There's an SP1 for SQL Server 2008 R2 that you can apply: https://www.microsoft.com/download/en/details.aspx?id=26727
Ahh okay thanks for that. My version already shows the same number as that contained within the SP1 link, so I must already have SP1.Still not sure why this time-out would occur though. See the example query below: |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-25 : 10:20:52
|
| What happens if you set the execution time out to 0 ?PBUH |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-10-25 : 10:34:59
|
quote:
Originally posted by Sachin.Nand
What happens if you set the execution time out to 0 ?
Still times out with the same error! |
|
|
|
ssdeveloper
Starting Member
37 Posts |
Posted - 2011-10-25 : 11:05:34
|
| Hi I didn't get or try on this kind of error before, but just want to work with you on this, did you check in the query options under query tab --> advanced --> set lock timeout option? |
|
|
|
paultech
Yak Posting Veteran
79 Posts |
Posted - 2011-10-25 : 11:06:32
|
| Hello try this below,Changing Command Timeout in Server:In the object browser tree right click on the server which give you timeout and select "Properties" from context menu.Now in "Server Properties -....." dialog click on "Connections" page in "Select a Page" list (on left side). On the right side you will get propertyRemote query timeout (in seconds, 0 = no timeout):[up/down control]you can set the value in up/down control.You can go to original post belowhttp://stackoverflow.com/questions/1137190/changing-the-commandtimeout-in-sql-management-studiopaul Tech |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-10-25 : 14:13:22
|
in your command window doping [serveriphere] -t How does the server respond?If you don't have the passion to help people, you have no passion |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-10-26 : 03:48:48
|
quote:
did you check in the query options under query tab --> advanced --> set lock timeout option?
That value is set to -1. However, I see if I open a new query I get a new menu item, and within that there is a separate EXECUTION TIME-OUT value option. If I change this, then my query time-out does change. However I presume its only for that particular query? Still seems odd as I need to set EVERY query to have a greater time-out value than 10 seconds.quote:
Changing Command Timeout in Server:
This value is set to 600 seconds (the default I believe)quote:
in your command window doping [serveriphere] -t
I assume you mean a DOS prompt window? If so, then I get 0% loss. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-10-26 : 04:28:09
|
| Queries executed from Management Studio normally don't time out, they just keep on running until the query is finished, you stop it manually or you kill the connection. Can you post a new screenshot (a working one) of the ssms query window and the error message?- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-10-26 : 05:09:40
|
| Hi LumbagoThe result is exactly as shown in the previous screen grab. Note as above if I edit the time-out value in the QUERY menu item for THAT PARTICULAR query, it doesn't time out. However SSMS is ignoring the value when configured within TOOLS > OPTIONS and times out after 10 seconds regardless of the value entered. |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-10-26 : 05:13:46
|
See screen shot when setting time-out in QUERY menu item (works okay): |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-10-26 : 06:10:46
|
| I have never messed with these timeout values and I don't see any reason why I should. I have both settings default to 0 and it has been working great the last 10 years or so...- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
|
Queries time out after 10s regardless of settings