SQL Server Management Studio: Increase query timeout

What are the options to configure remote query timeout?

Prerequisites

  • Remote server connections must be allowed before this value can be set.

  • Security Permissions : 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.

    Using SQL Server Management Studio

  1. Connect to MS SQL server via SQL Management Studio.
  2. In Object Explorer, right-click on the server name and then select Properties.

image  p.jfif

  1. In the new tab, click on Connections node.
  2. In Remote Query Timeout change it to your desired value or specify 0 to set no limit.


image pp.jfif

5. Click on OK to save the changes.


Using Transact-SQL

  1. Connect to MS SQL server via SQL Management Studio.
  2. From the Standard bar, click on New Query.
  3. 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
  4. Click Execute.