SQLTeam.com | Weblogs | Forums

Why is there an SQL Server remote connection timeout?

Hi Guys

I'm using SSMS 2012.

Does anyone know why there is a default time out on SQL Servers of 600 seconds?

I've increased it to 1800 seconds, in case I take a break for lunch, for example.

But I was wondering whether therew would there be a problem with increasing it?

I'm thinking of setting it at 3600 second.

Is it not recommended to change it to a high number?

TIA

What is the requirement that needs to have that setting changed? Is something timing out?

Yes, SSMS times out if you haven't been active for 600 seconds.

So if you needed to do some working in another application, you HAVE TO connect to the server again (manually), which is annoying.

Wait. In 2012? Where are you setting this timeout?

Using SQL Server Management Studio

  1. In Object Explorer, right-click a server and select Properties.
  2. Click the Connections node.
  3. 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 .

I think this isn't saying exactly that. This is for remote server connections from other SQL Servers.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-properties-connections-page?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev15.query%3FappId%3DDev15IDEF1%26l%3DEN-US%26k%3Dk(sql13.swb.serverproperties.connections.f1)%26rd%3Dtrue%26f%3D255%26MSPPError%3D-2147217396&view=sql-server-ver15

Books Online says (I marked the relevant bit in italics):

Remote Server Connections

Allow remote connections to this server
Controls the execution of stored procedures from remote servers running instances of SQL Server. Selecting this check box has the same effect as setting the sp_configureremote access option to 1. Clearing it prevents execution of stored procedures from a remote server.

Remote query timeout (in seconds, 0 = no timeout)
Specifies how long (in seconds) a remote operation may take before SQL Server times out. The default is 600 seconds, or a 10-minute wait.

Require distributed transactions for server-to-server communication
Protects the actions of a server-to-server procedure through a Microsoft Distributed Transaction Coordinator (MS DTC) transaction. For more information, see Configure the remote proc trans Server Configuration Option.

I do think Azure boots you out if you've been connected too long. But I don't think it's that setting.