Connection pooling issue - Timeout expired.

M

mapexvenus

I have a .NET application that uses SQL server. After some useage I get
the following error:

"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"

I looked at the SQL server process info and found process ID's for the
procedures that the application called - they all had the status as
'sleeping'. The timeout expired error happens when the number of
processes reaches the max pool size.

My questions are:

1. why arent these sleeping proecsses processes being reused since all
connections are being closed?
2. Will increasing the max pool size fix this problem?
 
J

Joyjit Mukherjee

Hi,

The garbage collection mechanism in .NET is highly indeterministic, i.e. you
cannot make sure when an object is disposed so, try closing your connection
objects in a structured manner i.e., preferably in the finally block, which
is sure to execute.

Increasing the max pool size will enable more live objects in pool, which
will somewhat improve the performance.

Let me know if you need anything else.

regards
Joyjit
 
M

mapexvenus

Thank you for your reply. Do you have any idea about those sleeping
SQL Server processes?? What does it mean when a process is 'sleeping'
and why arent these being used?

Thanks!
 
J

Joyjit Mukherjee

Hi,

the status column for a process (identified by a PID) is set to 'SLEEP' when
the timeout for that process is over. As soon as SQL Server receives any
command from that PID, the status is set to 'AWAITI' first, then 'RUNNABLE'.

regards
Joyjit
 
M

mapexvenus

Thanks, so why doesnt that process become available for use again? Isnt
that what a connection pool is supposed to do?
 
B

bruce barker

you are failing to close connections, and thus return them to the pool for
reuse. when the pool is full, the pool manager waits awhile to see if a
connection will be returned to the pool, if not it give a timeout error.

-- bruce (sqlwork.com)


| I have a .NET application that uses SQL server. After some useage I get
| the following error:
|
| "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"
|
| I looked at the SQL server process info and found process ID's for the
| procedures that the application called - they all had the status as
| 'sleeping'. The timeout expired error happens when the number of
| processes reaches the max pool size.
|
| My questions are:
|
| 1. why arent these sleeping proecsses processes being reused since all
| connections are being closed?
| 2. Will increasing the max pool size fix this problem?
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top