Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
This article describes how to configure the remote query timeout
server configuration option in SQL Server by using SQL Server Management Studio or Transact-SQL.
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 is a 10-minute wait. Setting this value to 0
disables the time-out. 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. A query waits until it completes.
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.
Note
The remote query timeout
server configuration setting is unrelated to connection and query time-out errors. For more information, see Troubleshoot query time-out errors.
Remote server connections must be allowed before this value can be set.
Execute permissions on sp_configure
with no parameters or with only the first parameter are granted to all users by default. To execute sp_configure
with both parameters to change a configuration option or to run the RECONFIGURE
statement, a user must be granted the ALTER SETTINGS
server-level permission. The ALTER SETTINGS
permission is implicitly held by the sysadmin and serveradmin fixed server roles.
In Object Explorer, right-click a server and select Properties.
Select the Connections node.
Under Remote server connections, in the Remote query timeout box, type or select a value from 0 through 2,147,483,647 to set the maximum number seconds for SQL Server to wait before timing out.
Connect to the Database Engine.
From the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute. This example shows how to use sp_configure to set the value of the remote query timeout
option to 0
to disable the time-out.
USE master;
GO
EXECUTE sp_configure 'remote query timeout', 0;
GO
RECONFIGURE;
GO
For more information, see Server configuration options.
The setting takes effect immediately without restarting the server.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Configure SQL Server resources for optimal performance - Training
Configure SQL Server resources for optimal performance
Documentation
Troubleshoot query time-out errors - SQL Server
This article describes how to troubleshoot the time-out errors when you run slow-running queries.
Timeout expired messages when connecting to SQL Server - SQL Server
Fixes timeout expired errors when you connect to SQL Server and helps you verify and troubleshoot the errors.
Server configuration: remote login timeout - SQL Server
Learn about the remote login timeout option. See how it limits the number of seconds that SQL Server allots for connecting to a remote server.