0

I have an ASP.NET Web API application running on an ARR hosted 3 server IIS web cluster. Every once in a while the SqlConnection starts timing out after the default 30 seconds, the exception caught is

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding

This happens across all 3 servers and once this starts it continues until we notice, sometimes hours later. The only way we have currently found to resolve the situation is to recycle the app pool on any one of the 3 web servers. Immediately after doing this the time outs stop and everything continues as normal.

Here's the code used to make the connection. The ActionsSP is just looking up a couple of values and then inserting into a table and has been analysed in SSMS to be optimum.

try
{
    using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString))
    {
        sqlConnection.Open();

        using (SqlCommand myCommand = new SqlCommand("ActionsSP", sqlConnection) { CommandType = CommandType.StoredProcedure })
        {
            myCommand.Parameters.AddWithValue("@pTimestamp", dtTimestamp);
            myCommand.Parameters.AddWithValue("@pDomain", LUDomain);
            myCommand.Parameters.AddWithValue("@pOperationId", nOperationId);
            myCommand.Parameters.AddWithValue("@pResultId", nResultId);

            SqlParameter paramError = new SqlParameter { ParameterName = "sError", SqlDbType = SqlDbType.VarChar, Size = 1024, Direction = ParameterDirection.Output };
            myCommand.Parameters.Add(paramError);

            myCommand.ExecuteNonQuery();

            if (!Convert.IsDBNull(paramError.Value) && !string.IsNullOrEmpty(paramError.Value.ToString()))
                throw new ArgumentException(paramError.Value.ToString());
        }
    }
}
catch (Exception ex)
{
    sError = ex.Message;
}

Above is one example but this has happened several times on 2 different server cluster using different SQL Server databases. My suspicions have pointed to the connection pools but I'm struggling to understand how I can prove this and what I can do to stop it re occurring, If anyone could offer any advice or help I would be eternally grateful

9
  • Computer are multitasking and you cannot control the time it takes to do a task. The solution is to increase the TimeOut in the SQL Command (default 30 seconds) so you do not get an error.
    – jdweng
    Commented Aug 10, 2023 at 16:55
  • We had a similar issue where the sqlconnection would time out and had to restart the app pool. I updated my code to use asynchronous open and it fixed the issues. Please see this link: stackoverflow.com/questions/40662788/…. Commented Aug 10, 2023 at 17:03
  • 2
    If recycling the app pool on a single server fixes the issue occurring on all 3 servers, I don't think it's related to client-side connection pooling. Have you monitored SQL activity during the problem? The recycle would kill all queries from that server, which would clear long-term blocking or runaway queries executed from that server that might impact queries from the other servers.
    – Dan Guzman
    Commented Aug 10, 2023 at 17:33
  • 1
    Slight detour.....dbdelta.com/addwithvalue-is-evil Surprised @DanGuzman didn't take the opportunity to post it himself. ;)
    – Sean Lange
    Commented Aug 10, 2023 at 18:18
  • 1
    Perhaps something else that is running takes a lock which blocks rest of the queries. Recycling would kill that "whatever" which then unblocks the database. You should check sysprocesses or the newer views to see if the blocking occurs and go from there Commented Aug 11, 2023 at 9:32

0

Browse other questions tagged or ask your own question.