How to make SqlConnection timeout more quickly

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);
   }
}

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.


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.


Comments

  1. Ridge

    • 2020/9/2

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

  2. Rhett

    • 2017/3/9

    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

  3. Jasper

    • 2019/10/11

    You can read more at ConnectionString; SqlConnection aConnection = new You can set ConnectionTimeout only you create a instance.

  4. Enzo

    • 2017/3/28

    Data Source = server;Initial Catalog=databaseUser Id=username;Password=password;Connect Timeout=30. Note that the value is expressed in seconds, not milliseconds. The default value is 30 seconds. Secondly, we can use the SqlCommand.CommandTimeout value. This sets the timeout value of a specific query running on SQL Server.

  5. Ayden

    • 2018/5/24

    The primary timeout value is that of SqlConnection. QuickOpen (in lack of a better name, it isn't quicker, it simply fails quicker).

  6. Michel

    • 2019/11/9

    Refer to the user guide, how to change the connection string in embedded dashboard at run time and follow the given steps: Configure the connection string to increase the connection time-out and define the pool size.

  7. Hasani

    • 2016/10/31

    Thanks. sample script. //USE ADO.NET Connection from SSIS Package to get data from table SqlConnection myADONETConnection = new SqlConnection();

  8. Khalil

    • 2018/11/21

    The code opens the connection and displays the ConnectionTimeout property in the console window. C#. using Microsoft.Data.SqlClient; class Program { static void Main() { OpenSqlConnection (); Console.ReadLine (); } private static void OpenSqlConnection() { string connectionString = GetConnectionString (); using (SqlConnection connection = new SqlConnection (connectionString)) { connection.Open (); Console.WriteLine ("State: {0}", connection.State); Console.WriteLine ("ConnectionTimeout:

  9. Matias

    • 2015/1/19

    Most accepted method of doing this is through the CommandTimeout property of the SqlCommand object. Set it to 0 for an infinite timeout.

  10. Yahya

    • 2017/2/19

    On the flip side: If connection is configurable, reduce connection string timeout to 1 second - that will make it easier. Fill the table with oodles of data and have 3 other processes spin in a loop updating chunks of that table with a transaction around the loop. Do not alter actual procedure called by the app (injecting waitfor).

  11. Richard

    • 2018/3/9

    I'm trying to do a data compare on a single table between 2 servers. This runs every hour and works most of the time.

  12. Princeton

    • 2019/2/13

    I broken my network to database for this test, but it's no working to set the connection timeout 3000 or 3. It will throw exception after a long time about more than 15 seconds. (I guess it's because the network timeout) I want to test it in 3 seconds. so is there a good way to do this.

  13. Connor

    • 2020/1/20

    CommandTimeout = 60; //The time in seconds to wait for the command to execute. You can read more at By code you can get connection in this way:

  14. Cameron

    • 2017/5/22

    If you are experiencing command timeout or execution timeout errors, then you have to increase the command timeout in your SQL Server Service Instance. If it is a connection timeout error, then you add the connection timeout property in the Extra field:

  15. Morel

    • 2017/12/16

    Since most questions I get about connection strings are related to SQL I've added Connection Timeout=60 to allow the SQL connection 60 

  16. Carl

    • 2019/4/8

    NET is 8,192 bytes, which is sometimes rather more than you need. As String I The OleDbConnection and SqlConnection objects have just a few more.

  17. Greyson

    • 2020/12/30

    Solved: My query is taking more time to refresh and when i refresh im let Source = Sql.Databases("Servername", [CommandTimeout=#duration(0, 0, 35, 0)]), 

Comments are closed.

Recent Posts