This was an incoming query for the database engine of default instance and hence Remote Query Timeout Settings (which is set to 5 sec(s)) did not apply.
If there is no value specified for connection timeout in the connection string then the default value is 30. When the timeout expires then the thread will continue, but it will do so having reported a connection failure. This time query is still waiting even though it crossed 12 sec(s) without getting timed out. The connection timeout is measured in seconds from the point the connection is opened. Both the queries are incoming for the default instance. Open a different session for the default instance and run select on the same table. This time executing it in the default instance. Keep and open transaction on the same table. Now lets see how it works for incoming query. So, it proves that the remote query time out settings applies for remote query (i.e. If you can see that the query got timed out exactly after 5 sec(s). Initiate a remote query from the default instance (Outgoing Query). In Linked server I am keeping an open transaction so that the lock on the table. Now if I run a remote query to linked server that would be an outgoing query for the default instance and incoming for Linked Server. Kept the default settings for the Linked Server SQL Instance (SQL_Named). This could be because the pre-login handshake failed or the server was unable to respond back in time. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This should have displayed in the connection string and prevented the error they received. Class11 ErrorCode-2146232060 HResult-2146232060 LineNumber0 MessageConnection Timeout Expired. You can execute the below query to set ‘Remote Query Timeout’ settings: sp_configure 'Remote Query Timeout', 5 Setting the ‘Connect Timeout’ to 0 gives the SSIS package an unlimited amount of time to attempt connection. This should get clarified once you read through this article.Īs you can see in the above screen shot, I have set the remote query timeout setting for the default instance to 5 sec(s). The misunderstood part here is, what is outgoing and what is incoming query for SQL Server. It clearly says, this applies to only outgoing connection and does not have any effect on incoming queries.
This value has no effect on queries received by the Database Engine. This value applies to an outgoing connection initiated by the Database Engine as a remote query. The default is 600, which allows a 10-minute wait. Use the remote query timeout option to specify how long, in seconds, a remote operation can take before Microsoft SQL Server times out. This does not work the way you might think it does. I have seen whenever customer complains about Query Time Out issue, there is a tendency to play around with this settings.
0 kommentar(er)
