Correctly understand SQL Server configuration timeout related options

Understanding SQL Server correctly configuration Options "Remote login timeout "and" Remote query timeout "

View settings for configuration options

sp_configure

650) this.width=650; "title=" clip_image001 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "Alt=" Clip_ image001 "src=" http://s3.51cto.com/wyfs02/M02/89/20/wKiom1gIenaBz_7UAAATjNptfJk958.png "border=" 0 "height=" 100 "/ >

Telnet timeout

Reference: Https://msdn.microsoft.com/en-us/library/ms175136.aspx

"The remote login timeout option specifies the number of seconds to wait before returning from a failed attempt t o Log in to a remote server. For example, if the trying to log on to a remote server and the server is down, remote login timeout helps m Ake sure that you does not have a to wait indefinitely before your computer stops trying to log in. The default value for this option is ten seconds. A value of 0 allows for an infinite wait.

The default value for this option was seconds in SQL Server 2008.

The remote login timeout option affects connections to OLE DB providers made for heterogeneous queries.

The setting takes effect immediately without restarting the server. "

650) this.width=650; "title=" clip_image003 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "alt=" clip_image003 "src=" Http://s3.51cto.com/wyfs02/M00/89/20/wKiom1gIenfClZUqAADGAWUnw94103.jpg "border=" 0 "height=" 342 "/>

EXEC sp_configure ' remote login timeout ', 35; Goreconfigure; GO

Remote query timeout

Reference: Https://msdn.microsoft.com/en-us/library/ms189040.aspx

"The remote query timeout option specifies how long, in seconds, a remote operation can take before SQL Server ti Mes out. The default value for this option is which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has a effect on queries received by the Database Engine. To disable the time-out, set the value to 0. A query would wait until it is canceled.

For heterogeneous queries, remote query timeout Specifies the number of seconds (initialized in the command objec T using the DBPROP_COMMANDTIMEOUT rowset property), a remote provider should wait for result sets before the query Tim Es out. This value was also used to set dbprop_generaltimeout if supported by the remote provider. This would cause any and operations to time off after the specified number of seconds.

For remote stored procedures, remote query timeout Specifies the number of seconds that must elapse after sending A remote EXEC statement before the remote stored procedure times out.

The setting takes effect immediately without restarting the server.

”

EXEC sp_configure ' remote query timeout ', 0; Goreconfigure; GO

Options for remote server and linked server

Reference: Https://msdn.microsoft.com/en-us/library/ms178532.aspx

sp_serveroption [@server =] ' server ', [@optname =] ' option_name ', [@optvalue =] ' option_value ';

Connect Timeout	Time-out valuein seconds for connecting to a linked server. If 0, use the sp_configure default.
Query timeout	Time-out value for queries against a linked server. If 0, use the sp_configure default.

I simply explain that these options are only for "outgoing connections".

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1863914

Correctly understand SQL Server configuration timeout related options