How to make SqlConnection timeout more quickly

Question

I am using an SQL connection string with SqlClient.SqlConnection and specifying Connection Timeout=5 in the string, but it still waits 30 seconds before returning failure. How do I make it give up and return faster? I'm on a fast local network and don't want to wait 30 seconds. The servers that are not turned on take 30 seconds to fail. This is just a quick utility program that's going to always run just on this local network.

Edit: Sorry if I was unclear. I want the SqlConnection.Open to fail more quickly. Hopefully that could be deduced from the fact that the servers I want to fail more quickly are turned off.

Edit: It seems that the setting only fails sometimes. Like it knows the IP address of the server, and is using TCP/IP to talk to it (not local) but can't contact SQL Server at that address? I'm not sure what the pattern is, but I don't see the problem when connecting locally with SQL Server stopped, and I don't see it when attempting to connect to a non-existent server. I have seen it when attempting to contact a server where the Windows 2008 firewall is blocking SQL Server, though.

Answer 1

It looks like all the cases that were causing long delays could be resolved much more quickly by attempting a direct socket connection like this:

foreach (string svrName in args)
{
   try
   {
      System.Net.Sockets.TcpClient tcp = new System.Net.Sockets.TcpClient(svrName, 1433);
      if (tcp.Connected)
         Console.WriteLine("Opened connection to {0}", svrName);
      else
         Console.WriteLine("{0} not connected", svrName);
      tcp.Close();
   }
   catch (Exception ex)
   {
      Console.WriteLine("Error connecting to {0}: {1}", svrName, ex.Message);
   }
}

I'm going to use this code to check if the server responds on the SQL Server port, and only attempt to open a connection if it does. I thought (based on others' experience) that there would be a 30 second delay even at this level, but I get a message that the machine "actively refused the connection" on these right away.

Edit: And if the machine doesn't exist, it tells me that right away too. No 30-second delays that I can find.

Edit: Machines that were on the network but are not turned off still take 30 seconds to fail I guess. The firewalled machines fail faster, though.

Edit: Here's the updated code. I feel like it's cleaner to close a socket than abort a thread:

static void TestConn(string server)
{
   try
   {
      using (System.Net.Sockets.TcpClient tcpSocket = new System.Net.Sockets.TcpClient())
      {
         IAsyncResult async = tcpSocket.BeginConnect(server, 1433, ConnectCallback, null);
         DateTime startTime = DateTime.Now;
         do
         {
            System.Threading.Thread.Sleep(500);
            if (async.IsCompleted) break;
         } while (DateTime.Now.Subtract(startTime).TotalSeconds < 5);
         if (async.IsCompleted)
         {
            tcpSocket.EndConnect(async);
            Console.WriteLine("Connection succeeded");
         }
         tcpSocket.Close();
         if (!async.IsCompleted)
         {
            Console.WriteLine("Server did not respond");
            return;
         }
      }
   }
   catch(System.Net.Sockets.SocketException ex)
   {
      Console.WriteLine(ex.Message);
   }
}

Answer 2

Update 2 I suggest rolling your own timeout. Something like this:

internal static class Program
{
    private static void Main(string[] args)
    {
        Console.WriteLine(SqlServerIsRunning("Server=foobar; Database=tempdb; Integrated Security=true", 5));
        Console.WriteLine(SqlServerIsRunning("Server=localhost; Database=tempdb; Integrated Security=true", 5));
    }

    private static bool SqlServerIsRunning(string baseConnectionString, int timeoutInSeconds)
    {
        bool result;

        using (SqlConnection sqlConnection = new SqlConnection(baseConnectionString + ";Connection Timeout=" + timeoutInSeconds))
        {
            Thread thread = new Thread(TryOpen);
            ManualResetEvent manualResetEvent = new ManualResetEvent(false);
            thread.Start(new Tuple<SqlConnection, ManualResetEvent>(sqlConnection, manualResetEvent));
            result = manualResetEvent.WaitOne(timeoutInSeconds*1000);

            if (!result)
            {
                thread.Abort();
            }

            sqlConnection.Close();
        }

        return result;
    }

    private static void TryOpen(object input)
    {
        Tuple<SqlConnection, ManualResetEvent> parameters = (Tuple<SqlConnection, ManualResetEvent>)input;

        try
        {
            parameters.Item1.Open();
            parameters.Item1.Close();
            parameters.Item2.Set();
        }
        catch
        {
            // Eat any exception, we're not interested in it
        }
    }
}

Update 1

I've just tested this on my own computer using this code:

internal static class Program
{
    private static void Main(string[] args)
    {
        SqlConnection con = new SqlConnection("Server=localhost; Database=tempdb; Integrated Security=true;Connection Timeout=5");
        Console.WriteLine("Attempting to open connection with {0} second timeout, starting at {1}.", con.ConnectionTimeout, DateTime.Now.ToLongTimeString());

        try
        {
            con.Open();
            Console.WriteLine("Successfully opened connection at {0}.", DateTime.Now.ToLongTimeString());
        }
        catch (SqlException)
        {
            Console.WriteLine("SqlException raised at {0}.", DateTime.Now.ToLongTimeString());
        }
    }
}

and it obeys the Connection Timeout value in the connection string. This was with .NET 4 against SQL Server 2008 R2. Admittedly, it's a localhost connection which may give different results but it means I can't replicate the problem.

I can only suggest trying a similar chunk of code in your network environment and seeing if you continue to see long timeouts.

Old (incorrect) answer I incorrectly thought the ConnectionTimeout property was settable, but it isn't.

Try setting SqlConnection.ConnectionTimeout instead of using the connection string.

Answer 3

The Command Timeout and the Connection Timeout are two different things.

SqlConnection.ConnectionTimeout is "the time (in seconds) to wait for a connection to open. The default value is 15 seconds." Thats only used when you call SqlConnection.Open().

The SqlCommand.CommandTimeout does what you want to do.