正確理解SQL Server配置timeout相關選項

標籤：timeout   remote login timeout   remote query timeout   

正確理解SQL Server配置選項“remote login timeout”和“remote query timeout”

查看配置選項的設定

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" />

 

遠程登入逾時

參考：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 to log in to a remote server. For example, if you are trying to log in to a remote server and that server is down, remote login timeout helps make sure that you do not have to wait indefinitely before your computer stops trying to log in. The default value for this option is 10 seconds. A value of 0 allows for an infinite wait.

The default value for this option is 20 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

 

遠程查詢逾時

參考：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 times out. The default value for this option is 600, 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 no effect on queries received by the Database Engine. To disable the time-out, set the value to 0. A query will wait until it is canceled.

For heterogeneous queries, remote query timeout specifies the number of seconds (initialized in the command object using the DBPROP_COMMANDTIMEOUT rowset property) that a remote provider should wait for result sets before the query times out. This value is also used to set DBPROP_GENERALTIMEOUT if supported by the remote provider. This will cause any other operations to time out 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

 

遠程伺服器和連結的伺服器的對應選項

參考：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.

我只簡單解釋一句，這幾個選項只是針對“outgoing connections”。

本文出自 “SQL Server Deep Dive” 部落格，請務必保留此出處http://ultrasql.blog.51cto.com/9591438/1863914

正確理解SQL Server配置timeout相關選項