CODEWITHSUNDEEP
c#sqlconnection
Girish
Girish

Reputation: 447

C#: Root cause of Max pool size was reached

We are getting following error in our WCF application.

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.

When we received the error at the time total 24 connection were consumed by the application and I think 100 is a default connection pool size.

We always close the SQL connection and also we dispose the SQLDataReader.

I am not sure why this execution occurred. Is there any other situation when we get this error?

Upvotes: 3

Views: 20643

Answers (1)

Ricardo Pontual
Ricardo Pontual

Reputation: 3757

I have some sugestions.

  1. Implement correctly all connections inside using blocks to close/dispose connections (as you said, this is already done)
  2. Check which user/machines are keeping opened connections. Run this query to identify the database id:

select distinct dbid, DB_NAME(dbid) FROM sys.sysprocesses where dbid > 0

Then, use this query to inspect all opened connections, replacing the dbid:

SELECT dbid, DB_NAME(dbid) as DatabaseName, COUNT(dbid) as ConnectionCount, loginame as LoginName
  FROM sys.sysprocesses
 WHERE  dbid = 1
 GROUP BY dbid, loginame
 ORDER BY count(dbid) desc

This can give you some hint about who is keeping too much connections opened.

  1. Implement pooling in connection string to limit connections. Use this in your application connection string:

Pooling=true; Min Pool Size=1; Max Pool Size=5

I hope this can help you.

Upvotes: 3

Related Questions