Maximum Pool Size on using SQL connection

Question

I have the following code to check if a table exists:

var selectQuery = $"SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}'";

using (var conn = new SqlConnection(_sqlServerConnectionString.SqlServerConnectionString))
{
    conn.Open();
    using (var cmd = new SqlCommand(selectQuery, conn))
    {
        var result = (int)cmd.ExecuteScalar();
        return result > 0;
    }
    conn.Close();
}

this is called multiple times. On running this, I see:

The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

What am I missing? I have closed connection so not sure what's missing?

You have conn.Close after the the return statement, which means connection is not getting closed. You can try moving conn.Close before the return. — Alok, Apr 14 '22 at 03:04
Thank you! I will try that. Quick question though. I tried without conn.Close() as my understanding is that is I use using (var conn), connection gets automatically closed. Please let me know if that is correct. — user989988, Apr 14 '22 at 05:39
The explicit `close` is not needed. The connection will automatically be closed/disposed when it goes out of scope of the `using` block. The error could be due to a leak elsewhere or if initial connections are very slow and you have many concurrent executions of the method. — Dan Guzman, Apr 14 '22 at 10:18
When I don’t explicitly close connection, I see max pool size error. — user989988, Apr 14 '22 at 15:26
@Alok This helped. However, I see connection timeout issue happening occasionally. Any idea what I am missing? Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=17; handshake=134; [Login] initialization=0; authentication=0; [Post-Login] complete=29204; The wait operation timed out. — user989988, Apr 25 '22 at 20:29

score 0 · Answer 1 · answered Apr 14 '22 at 14:38

This probably seems like a leak somewhere else. Also, if some kind of error is happening between your open and close code then your connections will keep piling up as the execution will not exit the using block. Please refer this another stack overflow link:- How can I solve a connection pool problem between ASP.NET and SQL Server?

Maximum Pool Size on using SQL connection

1 Answers1