Help! .NET connection pool maxout

  • Thread starter Thread starter Wayne Xu
  • Start date Start date
W

Wayne Xu

I am maintaining a "real time report" system written in .NET/SQL server, it
include one updater filling data into tables and a web application pooling
data from the tables. Both running in the same time. Web application create
a new SqlConnection object when the page needs pooling data. It closes the
connection once completed. To ensure the report's accuracy, I have to use
"set isolation level to serializable" in the SQL stored procedure.
There are everage 20 users and each user hit the database everay 3 minutes.
Updater refills the tables every minute.

The system works fine in most time of the day, but web brower suddenly
started timeout.
the error is "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 this is
happen, you can find over 50 locks in SQL server. It can be cleared by
restart IIS, or renew the web application (means reload).

The problem happened in everage once a night, but with no specific time.

I went to google and found similar question and answer, I followed the
suggestion to set connection object to null, but problem still exists. (try
http://www.google.com/search?hl=en&...eout+connection+pool+full+&btnG=Google+Search
and click on "Connection Pools maxed out?" )

Any idea?
 
[snip]
I went to google and found similar question and answer, I followed the
suggestion to set connection object to null, but problem still exists.
Click to expand...

The googled answer is proof that there are many possible answers to any
problem, and only a subset of them are correct. Setting a connection
reference to null (or Nothing in VB) will not help you. The problem is not a
lack of memory resources, but a lack of dataabse connections.

Make sure that you're properly disposing of your connections. If you're
using a connection with C#, your code should look something like:
using(SqlConnection con = new SqlConnection(...))
{
// use connection here.
}
 
Sure the news group should be much better than google. That's why I switch
to here and found you :-)

By my understanding, the suggestion from google is also not concerned with
the lack of memory, what the point is they are suspecting the GC can not
handle SQL connection well.

I didn't use "using...".
Now I have made changes and put into production. See what will happen in
this week.

Thanks!

Wayne Xu

Mickey Williams said:
[snip]
I went to google and found similar question and answer, I followed the
suggestion to set connection object to null, but problem still exists.
Click to expand...

The googled answer is proof that there are many possible answers to any
problem, and only a subset of them are correct. Setting a connection
reference to null (or Nothing in VB) will not help you. The problem is not a
lack of memory resources, but a lack of dataabse connections.

Make sure that you're properly disposing of your connections. If you're
using a connection with C#, your code should look something like:
using(SqlConnection con = new SqlConnection(...))
{
// use connection here.
}
Click to expand...
 
Mickey,

I followed your suggestion, put "using" clause in all places. The problem
improved, but it happened again today. It has been running smoothly over a
whole week.

Do you have any other suggestions?

Some hints:
I looked into SQL Server when this happens, the huge number of locks are all
with type "DB". My DBA is suspecting that, since DB type locks is highest
level lock. I found the lock is generated when I using querys with "set
isolation level serializable". I use this isolation level because the report
will not be accurate otherwise.

Any thought?

Mickey Williams said:
[snip]
I went to google and found similar question and answer, I followed the
suggestion to set connection object to null, but problem still exists.
Click to expand...

The googled answer is proof that there are many possible answers to any
problem, and only a subset of them are correct. Setting a connection
reference to null (or Nothing in VB) will not help you. The problem is not a
lack of memory resources, but a lack of dataabse connections.

Make sure that you're properly disposing of your connections. If you're
using a connection with C#, your code should look something like:
using(SqlConnection con = new SqlConnection(...))
{
// use connection here.
}
Click to expand...
 
Try disabling the pooling function (add ;pooling=false at the end of the connection string)

Also the connection must be closed to free up the SQL server - otherwise it just retains the process in a sleeping state

Look at the Process information under Management in the SQL Enterprise Manager and you will see what is happening!!

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
 
Back
Top