connecting to sql server failing intermittently with different different errors

Question

We have an app which is hosted on an on-premise infrastructure. The app takes around 20 calls per sec. There are 40 servers on which the app is installed in a load balanced fashion. Each servers hosts other 8 to 10 app on a shared basis. Out of all apps only one app is having SQL Server connectivity issues. The connectivity issues is intermittent and appears with many different different error messages. The app is taking 20 calls per sec and each call in turn might make around 10 SQL Server connections and runs simple select and insert queries.

We checked SSL settings on client and server and there is no issue. No DB table locks are on the DB Server. We checked DB server, network and everything is fine. No memory or CPU issue on any of the server. No single issue is observed for any other app installed on the same application servers.

The code running SQL queries is pretty simple:

private List<values> GetItemsFromDB(string queryString, SqlParameter[] sqlParams)
{
    var listOfItems = new List<Item>();

    using (SqlConnection connection = new SqlConnection(_provider.ConnectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);

        try
        {
            connection.Open();
            command.Parameters.AddRange(sqlParams);

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                var item = new Item()
                        {
                            Id = Convert.ToInt64(reader["Id"]),
                            SerializedConfigItem = reader["SerializedItem"] != DBNull.Value
                                ? reader["SerializedItem"].ToString()
                                : null,
                            SerializedItem2 = reader["SerializedItem2"] != DBNull.Value
                                ? reader["SerializedItem2"].ToString()
                                : null,
                            Created = reader["Created"] != DBNull.Value
                                ? Convert.ToDateTime(reader["Created"])
                                : DateTime.MinValue
                        };
                listOfItems.Add(item);
            }

            reader.Close();
        }
        catch (Exception ex)
        {
            _logHandler.Error(ex.Message, "Error while trying to get items from DB");
        }
        finally
        {
            connection.Close();
        }

        return listOfItems;
    }
}

private long WriteItemToDb(string queryString, SqlParameter[] sqlParams)
{
    long id = 0;

    using (SqlConnection connection = new SqlConnection(_provider.ConnectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);

        try
        {
            connection.Open();

            command.Parameters.AddRange(sqlParams);

            var obj = command.ExecuteScalar();
            id = Convert.ToInt64(obj);
        }
        catch (Exception ex)
        {
            _logHandler.Error(ex.Message, "Error while trying to insert/update/delete items to DB");
        }
        finally
        {
            connection.Close();
        }

        return id;
    }
} 

Below are some of the different error messages we get randomly and intermittently:

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A non-recoverable error occurred during a database lookup.)"

"The handle is invalid. (Exception from HRESULT: 0x80070006 (E_HANDLE))"

"A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The handle is invalid.)"

"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The handle is invalid.)"

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 9 - Associating port with I/O completion mechanism failed)"

Answer 1

Hi manojd-7499,

Welcome to Microsoft Q&A.
Could you please check if there are any error message in the event log?
What the version of SQL Server are you using? Please try to install the latest CU or SP for SQL Server.

Best Regards,
Amelia

Answer 2

Those errors are generic "could not connect" errors. They don't really help diagnose the real issue.

However, you mentioned load balancing. Depending on how your network is setup, you will get errors like this when you connect to server1 and then try to use that connection to server2. When load balancing SQL Server, you must use "sticky sessions" to guarantee the connection is always made to the same SQL Server. Otherwise you will get these kinds of errors.

This is almost certainly a networking issue of some kind.

Answer 3

I like to start with saying that my experience is that when you run into things that seem like a complete mystery, things are not really what you think they are. I have been involved in more than one thread where the cause for a mysterious problem has been something the poster did not tell me. Or the poster has insisted on something being X, when it in fact was Y. So there are all reason to double- and triple-check the information you give above.

But let's say that you are correctly relating the situation. You only have one application which is misbehaving. Then it must be something in that application. The connection string is the same, or at least that is the intention. But what if the connection string varies randomly? That could happen if you have code that writes to memory it should not write to. Now, this seems to be .NET, and one would like to think that memory-scribbling cannot happen in managed code. But maybe this application also has components written in unmanaged C++? Then again, one could argue if that if this would be the case, you would see random crashes all over the place. But maybe the memory-scribbling is confined to a location so that it only affects the connection string.

Maybe you should add logging to this application that traps and logs these errors together with the connection string being passed.