MS SQL Server has an option called Remote Query Timeout to specify the time (in seconds), a remote operation can take before SQL Server times out. Note that, it only applies to an outgoing (not incoming) connection initiated by the Database Engine as a remote query. The default value for this option is 600 seconds, which can be configured either via SQL Server Management Studio or Transact-SQL.
Using SQL Server Management Studio
- Connect to MS SQL server via SQL Management Studio.
- In Object Explorer, right-click on the server name and then select Properties.
- In the new tab, click on Connections node.
- In Remote Query Timeout change it to your desired value or specify 0 to set no limit.
- Click on OK to save the changes.
Using Transact-SQL
- Connect to MS SQL server via SQL Management Studio.
- From the Standard bar, click on New Query.
- Run the below query to set the Remote Query Timeout to 0 seconds ( 0 is unlimited). Similarly, you can replace 0 with your desired value (default is 600 seconds).
EXEC SP_CONFIGURE 'remote query timeout', 0 reconfigure EXEC sp_configure
- Click Execute.
