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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AndyJ
  • 11
  • 1
  • 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 Aug 10 '23 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: https://stackoverflow.com/questions/40662788/sqlconnection-open-vs-sqlconnection-openasync-whats-different-between-the-two. – SoftwareDveloper Aug 10 '23 at 17:03
  • Sql Server, by default implements connection pooling based on the unique connection strings, etc. – SoftwareDveloper Aug 10 '23 at 17:08
  • 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 Aug 10 '23 at 17:33
  • 1
    Slight detour.....https://www.dbdelta.com/addwithvalue-is-evil/ Surprised @DanGuzman didn't take the opportunity to post it himself. ;) – Sean Lange Aug 10 '23 at 18:18
  • You should look at the session wait stats on the server to see why the queries are timing out. It's possible that there is blocking that originates from a session on the IIS server you bounce. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-session-wait-stats-transact-sql?view=sql-server-ver16. – David Browne - Microsoft Aug 10 '23 at 18:42
  • You need to find the reason for the timeout, you can check whether there is a relevant message in the Event Viewer, if not, you need to debug the cause using IIS Debug Diagnostics Tool, this tool will generate detail log file, which will help you to identify the problem. – samwu Aug 11 '23 at 03:08
  • "has been analysed in SSMS to be optimum." so you claim. It would be my first suspicion to suspect the procedure, which might be suffering from parameter sniffing issues etc. But you haven't show it so can't say. – Charlieface Aug 11 '23 at 04:16
  • 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 – siggemannen Aug 11 '23 at 09:32

0 Answers0