locked
SQL Database Connection Timeout RRS feed

  • 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 8, 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
    • Proposed as answer by WeiLin Qiao Thursday, September 16, 2010 11:59 AM
    • Marked as answer by Alex Feng (SQL) Friday, September 17, 2010 10:37 AM
    Wednesday, September 8, 2010 11:02 PM

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 8, 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 8, 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
    • Proposed as answer by WeiLin Qiao Thursday, September 16, 2010 11:59 AM
    • Marked as answer by Alex Feng (SQL) Friday, September 17, 2010 10:37 AM
    Wednesday, September 8, 2010 11:02 PM
  • 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 9, 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 9, 2010 10:48 PM