Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed before the operation could be completed, or the server is not responding.

Question

I had an application that was functioning properly on an IIS server. However, after hosting it on an ECS fargate Linux container and conducting a load test, the ECS task crashes due to the following error:

Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed before the operation could be completed, or the server is not responding.

Also, I occasionally encounter the following error message:

System.InvalidOperationException: Timeout expired. The timeout period elapsed before obtaining a connection from the pool. This could be due to all pooled connections being in use and the maximum pool size being reached.

Even though my query has a proper index and a timeout of 5 seconds, it executes within only 20ms. Therefore, the query itself is not the cause of the issue.

Here is my connection string:

Data Source=x.x.x.x;Initial Catalog=DbName;User ID=Username;Password=mypassword;TrustServerCertificate=True

Previously, I had "MultipleActiveResultSets=True" in my connection string, but even after removing it, I still encounter the same error.

Here is my Dockerfile

FROM mcr.microsoft.com/dotnet/aspnet:7.0 AS base
WORKDIR /app
EXPOSE 80
# copy csproj and restore as distinct layers
FROM mcr.microsoft.com/dotnet/sdk:7.0 AS build
WORKDIR /src
COPY *.sln .
COPY ["PdfApp/PdfApp.csproj", "PdfApp/"]
RUN dotnet restore "PdfApp/PdfApp.csproj"
# copy and publish app and libraries
COPY . .
WORKDIR "/src/PdfApp"
RUN dotnet build "PdfApp.csproj" -c Release -o /app/build
FROM build AS publish
RUN dotnet publish "PdfApp.csproj" -c Release -o /app/publish /p:UseAppHost=false
FROM base AS final
WORKDIR /app
COPY --from=publish /app/publish .
ENTRYPOINT ["dotnet", "PdfApp.dll"]

Further technical details

Microsoft.Data.SqlClient version: 5.1.1
.NET target: Core 7.0
SQL Server version: SQL Server 2019)
Operating system: Linux Docker container

Please find below the connection configuration.

public interface IDbCon : IDisposable
{
    IDbConnection GetConnection { get; }
}

public class DbCon : IDbCon
{
    private readonly IConfiguration _config;
    private IDbConnection _connection;

    public DbCon(IConfiguration configuration)
    {
        _config = configuration;
        _connection = new SqlConnection(_config.GetConnectionString("ConStr"));
    }

    public IDbConnection GetConnection
    {
        get
        {
            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }
            return _connection;
        }
    }

    public void Dispose()
    {
        if (_connection.State == ConnectionState.Open)
        {
            _connection.Close();
        }
        _connection.Dispose();
    }
}

I'm utilizing Dapper to perform the data queries from the database.

public class Repository : IRepository
{
    private readonly IDbCon _conn;

    public Repository(IDbCon conn)
    {
        _conn = conn;
    }

    public async Task<IEnumerable<CustomList>> MyFunctionName()
    {
        IEnumerable<CustomList> list = new List<CustomList>();
        var con = _conn.GetConnection;
        try
        {
            list = await con.QueryAsync<CustomList>("spname", commandType: CommandType.StoredProcedure, commandTimeout: 30);
        }
        finally
        {
            con.Close();
        }
        return list;
    }
}

Answer 1

So you are saying that the query times out after 20 ms? Exactly how did you determine this? Did you measure this inside the application?

The message about not obtaining a connection from the pool suggest that you are not handling your connections object properly, but leave them open. Or you close them without disposing them explicitly, in which case garbage collection may not kick in often enough. You should always open connections in the using statement as in this example_

     using (SqlConnection cn = new SqlConnection(connstr)) {
         using (SqlCommand cmd = new SqlCommand()) {
            cmd.Connection = cn;
            cn.Open();

            cmd.CommandText = @"SELECT * INTO #temp FROM Orders";
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();

            cmd.CommandText = "SELECT * FROM #temp";
            int rowCount = 0;
            SqlDataReader reader = cmd.ExecuteReader();
               while( rowCount < 1000) { ++rowCount; };
         }
      }

Answer 2

you code is buggy. if any connection has not read all result sets the connection will not be closed properly. you do not show the Close method, but its probably as buggy as Dispose

    public IDbConnection GetConnection
    {
        get
        {
            if (_connection.State != ConnectionState.Open)
            {
                Close();
                _connection.Open();
            }
            return _connection;
        }
    }
    public void Close()
    {
        if (_connection.State != ConnectionState.Closed)
        {
            _connection.Close();
        }
    }
    public void Dispose()
    {
        Close();
        _connection.Dispose();
    }