[Disclaimer] : I think I have read every stackoverflow post about this already
I have been breaking my head over this for quite some time now. I am getting the following exception in my asp.net web.api.
Exception thrown: 'System.InvalidOperationException' in mscorlib.dll
Additional information: Timeout expired. 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.
Most people suggested that I should look for leaked connections in my application. Here is my code. Now I am sure that I am not leaking any connections
public async Task<IEnumerable<string>> Get()
{
var ds = new DataSet();
var constring = "Data Source=xxx;Initial Catalog=xxx;User Id=xxx;Password=xxx;Max Pool Size=100";
var asyncConnectionString = new SqlConnectionStringBuilder(constring)
{
AsynchronousProcessing = true
}.ToString();
using (var con = new SqlConnection(asyncConnectionString))
using (var cmd = new SqlCommand("[dbo].[xxx]", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@x1", 1);
cmd.Parameters.AddWithValue("@x2", "something");
await con.OpenAsync();
using (var rdr =await cmd.ExecuteReaderAsync())
{
if (rdr.HasRows)
{
ds.Load(rdr, LoadOption.OverwriteChanges, "MyTable");
}
rdr.Close();
con.Close();
ds.Dispose();
}
}
//I know this looks wrong, just an empty api method to show the code
return new string[] { "value1", "value2" };
}
The exception does not occur when I am using my local Sql Server. Only happens when I connect to our 'test server'. Are there anything else I can look at when trying resolve this issue. Like Sql server settings / network settings etc.
The stored procedure I call does not lock up the db I have checked for that as well. If that was the case it should have failed on my local Sql instance as well.
I am using jmeter to generate load, 1500 - threads(users). Surely I should be able to handle way more than that.
Thanks in advance
