SQLConnection timeout on IIS and not locallly

Question

I am facing weird behaviour on IIS 8.5. I am getting an error after I consume my endpoint few times (5-6 times): 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.

My code is written in net core 3.1, and connects to sql server and tries to execute a stored procedure. it works locally with any number of calls. Verified locally that no exception is being thrown.

 public int GetCount()
 {
        int jobCount = 0;
        using (SqlCommand com = new SqlCommand("dbo.GETCount", new SqlConnection(this.ConnectionString)) { CommandType = CommandType.StoredProcedure })
        {
            com.Parameters.Add(new SqlParameter("@jobCount", SqlDbType.Int) { Direction = ParameterDirection.Output });
            try
            {
                com.Connection.Open();
                com.ExecuteNonQuery();
                jobCount = (int)com.Parameters[0].Value;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                com.Connection.Close();
            }

        }

        return jobCount;
    }

Any suggestions?

Bruce Zhang · Accepted Answer · 2020-08-28 08:17:06Z

2

Here are my suggestions.

Always close your connection in the finally block
Increase pool size like in your connection string

string connectionString = "Data Source=localhost; Initial Catalog=Northwind;" +

"Integrated Security=SSPI; Min Pool Size=10; Max Pool Size=100";
Don't use pooling at all

string connectionString = "Data Source=localhost; Initial Catalog=Northwind;" +

"Integrated Security=SSPI; Pooling=false;";

answered Aug 28, 2020 at 8:17

Bruce Zhang

3,0921 gold badge7 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mikey Over a year ago

Do I need the finally block if the "using" block being used? thought.net core framework handles that by itself?also I thought pooling enhances performance

Bruce Zhang Over a year ago

If the object in using block implements the IDispose interface, there is no need to use finally block. The performance of pool not only depends on core framework, but also developer's code and configuration.

Collectives™ on Stack Overflow

SQLConnection timeout on IIS and not locallly

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related