Timeout expired because all pooled connections were in use

ASP.NET Maker General Discussion (ASP.NET Maker)
1

Facing the following error on calling API multiple times due to some connections are not closed.System.InvalidOperationException: ‘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.’

2

Make sure that you set sufficient time delay between API calls so that server resources can be re-used effectively.If problem persists, please provide more details such as what API actions were performed, time delay between API calls, database type and schema, etc. for further discussion.

3

Hello,
I am suffering from the same issue, but I cannot control the amount of request per second since this will be the Api of my public application and people will use it all the time.

My Api looks like this:

public class GetMyItems : ApiController
{
	[AllowAnonymous]
	[HttpGet("{type}")]

	public IActionResult Get([FromRoute] string type)
	{
		string sql = @"
						SELECT	*
						FROM	MyTable
						WHERE	type = 'x';
					";
		var result = ExecuteRows(sql);
		return Json(result);
	}
}

it works.Now the problem start after few requests (10 - 20), I receive the error:

‘System.InvalidOperationException’ occurred in System.Private.CoreLib.dll but was not handled in user code: '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.'Which clearly telling me that I have reached the maximum amount of allowed open connections to my database.
I have searched for that error and found that it may be caused by not closing the connection after opening it, so I have added CloseConnections(); before returning my Json:

var result = ExecuteRows(sql);
CloseConnections();
return Json(result);

Even though I feel it is something wrong to do in this way, but it increased the number of requests before getting the error to little more than double!Now how to solve this killing issue? We always hear about APIs that can handle thousands of requests per second… What is the wrong thing I am doing here which prevent me from requesting more than 25 - 35 requests per minute? :frowning:

Thanks in advance.

4

My specs are (for saving the question/answer time):

  • SQL Server 2017 running on Windows 10 with 64GB of ram and dual Xeon processor (so it is unlikely the error being caused by the hardware/resources limitation).
  • A very small table (8 columns, less than 200 records).
  • I get the error either from my app or even the simplest form of Postman calls.
5

Try adding garbage collection codes as well, e.g.

Collect();
return ...;
6

Ooh! That worked great!
I have successfully tested a thousand consecutive requests without any errors!
I love you guys!
Thank you, Michael

7

OMG!So this was the real issue.I thought I was going crazy with a recent application deployed on a client with about 1000 users.
By the end of the day, the server was running out of TCP ports.My first attempt was to hack the TCP / IP stack: TcpTimedWaitDelay, MaxUserPort, StrictTimeWaitSeqCheck
This has been fixed for the time being, but in SQL Server I had a bunch of open connectionsHere is a query to check to check this kind of open connections:

SELECT distinct TEXT, count(*)
FROM sys.dm_exec_connections c inner join sys.dm_exec_sessions s
on c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS ST 
where host_name = <IIS Server> and login_name = <SQL Login>
group by TEXT

The sleeping queries were only from the ApiController.Thanks for the tips, guys!

8

(still on v2020)
I want to raise another issue which I think is related to the API controllers
I have thousands of sleeping connections with “SET DATEFORMAT ymd”; most of them are triggered with the API calls.The above fix helped me with closing the API calls, but these are opened with an ExecuteAsync during the OpenConnectionAsync() Task

} else if (IsMsSql && DateFormatId > 0) { // DN
					await ExecuteAsync("SET DATEFORMAT ymd", c);

I have another project built with 2019 version and in that app I do not experience this plague, although I can find there couple of tens of sleeping connection with the same command: SET DATEFORMAT ymd

9

Make sure that you have used the latest template (Tools → Update Template). The codes from the latest template should be:

await c.ExecuteAsync("SET DATEFORMAT ymd");
10

Hi,Actually, it is the latest, I think I copied from the previous version.Not in aspnetfn.cs, in ANM2020 I got:

await c.ExecuteAsync("SET DATEFORMAT ymd");

Made some random calls in one of the API and all commands with SET DATEFORMAT ymd are sleeping.
The only way to kill them is to restart app pool/IIS, etc

11

So, I found out what is plaguing the API calls with sleeping ‘SET DATEFORMAT ymd’
I’ve enclosed the API in a security check

Security = CreateSecurity();
if (Security.IsLoggedIn) {
    //do the work
   return Json(rs);
}
else {
     return new JsonBoolResult(new {success = false, error = "Nice try, wanna-be hacker!" }, false);
}

the above SETs are adding on simple page loads, too, but at least I have identified where are these coming from in the API calls
If I comment the security check, I have fewer sleeping connections.

12

You can as well add the garbage collection codes before returning JSON in your API codes.

13

It is there, with CloseConnections, too.