none
SQL Database Connection Timeout

    Question

  • How do you modify the default 15 second timeout Connection Timeout on SQL databases in SQL 2005, for all users? I am able to modify the value on an individual basis through the options menu on the SQL Server 2005 login window however when signing on as a different user the changes do not carry to that users profile.
    Wednesday, September 08, 2010 1:14 PM

Answers

  • Remote login timeout has nothing to do at all with the SQLCLient Connections timeout. THe SQLConnection timeout is specified per connection and can be individual for each specific connection. It cannot be set on the server as if you think about it, the server couldn´t be available / reachable and the client might want to connect to the non-existing (and therefore non configured server) for a specific amount of time (seconds).

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Wednesday, September 08, 2010 11:02 PM
    Moderator

All replies

  • Hi.

    Here is an example to set the remote query timeout to 5 seconds on the SQL server:

    EXEC

     

    sys.sp_configure N'remote login timeout (s)', N'5'

    GO

    RECONFIGURE

     

    WITH OVERRIDE

    GO

     

     


    Kristian
    Wednesday, September 08, 2010 5:21 PM
  • Does this apply to the "Connection Timeout"  value which specifies in the connection string the amount of time SqlClient would wait after the connection request sent to server and before the handshake completes?
    Wednesday, September 08, 2010 5:29 PM
  • Remote login timeout has nothing to do at all with the SQLCLient Connections timeout. THe SQLConnection timeout is specified per connection and can be individual for each specific connection. It cannot be set on the server as if you think about it, the server couldn´t be available / reachable and the client might want to connect to the non-existing (and therefore non configured server) for a specific amount of time (seconds).

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Wednesday, September 08, 2010 11:02 PM
    Moderator
  • Is there any way for a sql db to reconginize when a the sql server has gone offline, perhaps in a failover operation and in a sense waits for the sql service to come back online and then the db reconnects? I am having trouble with failovers and was able to restore my sql server service but the db's remain offline even after the SQL Server service comes online.
    Thursday, September 09, 2010 2:03 PM
  • OK, I see two questions here:

    "Is there any way for a sql db to reconginize when a the sql server has gone offline, perhaps in a failover operation and in a sense waits for the sql service to come back online and then the db reconnects?"

    The general approach would be to wait a certain amount of time and then try to reconnect to the server. I wouldn´t query the server in addition to find out if its available.

    "I am having trouble with failovers and was able to restore my sql server service but the db's remain offline even after the SQL Server service comes online."

    If they are offline they were set to offline explicitly before. Databases don´t change their status spontanously to offline. So is that really the "(Offline)" descition you see as a suffix of the database name ?

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Thursday, September 09, 2010 10:48 PM
    Moderator