July 10, 2009 at 4:59 am
Hi,
I have a timeout of 600 sec. on my SQL Server 2005.
(i have seen it trought M.S.)
if i execute the command "ALTER DATABASE SGCT SET SINGLE_USER" and the database can not be set to Single_user mode, because there are other people connect to it, it don't raise an error after the 600c. (timeout) expires, it will trie until every body disconnnect form the database.
Should this command be finnish be the SQL Server if it don't executes with sucess within the remote timeout expires?
thank you
July 10, 2009 at 5:15 am
The remote timeout setting affects remote servers only. It has no effect on how long queries will wait on the local server.
If you want the alter database to kick everyone out either immediatly or after a certain amount of time, use the WITH ROLLBACK option on your alter database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2009 at 6:52 am
thanks for the reply Gail,
when you say:
"The remote timeout setting affects remote servers only. It has no effect on how long queries will wait on the local server.
Does this mean that this command only stops executing after the timeout expires, if the engine is on one server and the management studio from where i'm connecting is on other computer?
If the answer is yes, is there any parameter that can be configurated on SQL Server 2005 for timeout with local querys (Management Sudio on the same computer as the engine)?
thanks in advance.
July 10, 2009 at 7:09 am
river (7/10/2009)
Does this mean that this command only stops executing after the timeout expires, if the engine is on one server and the management studio from where i'm connecting is on other computer?
No. The remote timeout only affects commands that are remote to this server. ie commands that the SQL Instance with that configured on runs against a different server. Either via linked servers (openquery, 4-part naming) or ad-hoc remote calls (openrowset)
Timeout is always a client setting, so it's set on whatever the client is and if affects the remote server. If you want your management studio queries to timeout (why?), you need to set the timeout within the client, ie management studio.
SQL itself has no setting for timeouts of queries that it is running. It's always the client that specifies how long it is willing to wait.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 4:36 am
I'm sorry Gail, but i still didn't undestud something about this post.
"No. The remote timeout only affects commands that are remote to this server. ie commands that the SQL Instance with that configured on runs against a different server. Either via linked servers (openquery, 4-part naming) or ad-hoc remote calls (openrowset)
Timeout is always a client setting, so it's set on whatever the client is and if affects the remote server. If you want your management studio queries to timeout (why?), you need to set the timeout within the client, ie management studio.
"
i.e - suppose i have a server named SRV1 whitch is a Database server.
If i install only the client tools (management studio) on a desktop, when i connect to this server trought this GUI and make a query, this query is not affected by the remote timeout?
what about , if i have an application (VB app.) that is installed on my desktop. this app uses a connection string to connect to the server. when the app sends commands (SQL) to the server it is not affected my the remote timeout too?
Note - I configure remote timeout of this server on my machine. I register the SQL Server instance, trought the GUI (m.s) , then i click on properties in the instance registered. Then , choose connections option, and then i define the values.
July 13, 2009 at 7:05 am
river (7/13/2009)
If i install only the client tools (management studio) on a desktop, when i connect to this server trought this GUI and make a query, this query is not affected by the remote timeout?what about , if i have an application (VB app.) that is installed on my desktop. this app uses a connection string to connect to the server. when the app sends commands (SQL) to the server it is not affected my the remote timeout too?
No. Neither of those will be affected by the remote query timeout setting within SQL server. The timeout of those will be configured within the app's code. Management studio you can change it using the query options (Query menu) or in the login properties when you connect to the server. The VB app will have set it using the command timeout property somewhere in the code (ado). In either case it just applies to the connection that the setting is made on, no others.
The server doesn't know anything about a timeout. A timeout is what the connecting client specifies as the amount of time it is willing to wait for something to complete. SQL does not restrict how long it will run a query for.
The remote query timeout setting is used only when the SQL server that the setting is configured on connects to another database engine and runs a remote query. This is done across a linked server (with 4 part naming or OPENQUERY) or with an ad-hoc remote call OPENROWSET
Note - I configure remote timeout of this server on my machine. I register the SQL Server instance, trought the GUI (m.s) , then i click on properties in the instance registered. Then , choose connections option, and then i define the values.
That's the login and execution timeout, not remote timeout. Remote timeout is set using sp_configure.
exec sp_configure 'remote login timeout (s)'
exec sp_configure 'remote query timeout (s)'
Now, what exactly are you asking about, login timeout, execution timeout or remote timeout? They're three very different things.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2009 at 10:29 am
Gail, what about query wait. I quote from BOL:
Query Wait
Specifies the time in seconds (from 0 through 2147483647) that a query waits for resources before timing out. If the default value of -1 is used, the time-out is calculated as 25 times of the estimated query cost. For more information, see query wait Option.
July 14, 2009 at 10:51 am
That's only if a query is waiting for memory. Further quoting from Books Online.
In Microsoft SQL Server, memory-intensive queries (such as those involving sorting and hashing) are queued when there is not enough memory available to run the query. The query times out after a set time calculated by SQL Server (25 times the estimated cost of the query) or the time specified by the nonnegative value of the query wait.
So if that's set to say 30 sec, it won't force a query to stop running after 30 sec. It will only make a query that's waiting for a memory grant fail after waiting 30 sec. This is similar to the lock timeout, how long SQL will wait for a lock to be available before failing the query.
It is not a generic query timeout.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply