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