connection pool timeout expired - but I'm not leaking

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm getting the error message so many others have reported. I.e.

"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've read many posts on responses on the subject. The responses all seem to boil down to "you're leaking connections somehow." If I have read the documentation correctly, then I don't believe that is my problem, and here is what I've done to prove it to myself

I'm using Windows 2000 Server, SQL Server 2000 Developer's Edition, and Visual Studio.NET 2003. I'm completely up to date on all service packs for everything (except possibly if there's something newer than SP3 for SQL Server)

- I created a new blank Web Form. I used drag-and-drop in the Designer to add a sqlDataAdapter. In there, I selected for it to generate SQL statements, and I constructed a select statement that does "select top 1 * from requests where system_id>@system_id order by system_id". This process resulted in automatic generation of a sqlConnection object that also appears in Designer

- The connection is to a local SQL Server database

- I then used drag-and-drop to add a DataSet object to my form. I chose a typed dataset that was already part of my project, that was also generated by VS.NET

- I used drag-and-drop to add 4 server controls to my form - 3 labels and a button. The labels are databound to 3 columns in the dataset (again, all done visually - no coding so far). I created NO server-side handler for the button, so when you click it it just runs the Page_Load method and returns

- In the Page_Load method, I put in this code

if ( !IsPostBack
sqlDataAdapter1.SelectCommand.Parameters["@system_id"].Value = 0
els
sqlDataAdapter1.SelectCommand.Parameters["@system_id"].Value = lblSystemId.Text
sqlDataAdapter1.Fill( dsRequestTest1 )
DataBind()

The effect of this code is simply to have the page display the first record in the table when the page is first displayed. Each time I click the button on the page, it displays the next record and subsequent records from the database

Those 6 lines of code are the ONLY code I have added to this page

- When I run this page, I can click the button on the page 99 times and see the first 100 records in the table. When I click it the 100th time, I get the error

- I edited my sqlConnection object's connection string and added "max pool size=3". Now when I run it, I see the first 3 records, then get the error

- My understanding is that the sqlDataAdapter.Fill method opens and closes the sqlConnection itself, so I don't have to do an explicit Close. However, I tried it just to see what would happen. After the call to Fill(), I added these two lines

sqlConnection1.Close()
sqlDataAdapter1.SelectCommand.Connection.Close()

I still get the exact same behavior

- Also, I have tried using PerfMon to monitor the pooled connections in use, under the .NET CLR Data section of counters. It never shows any in use. The connection pool count goes from 0 to 1, but the pooled and unpooled connections in use stays at 0. This is for _global and all other instances of the counters

Help?!
 
Well, there are other reasons to cause this. Clearly, you're using connections faster than the system can free them. This CAN be caused by not closing a connection. However, consider that the Fill method will not close a connection when:
1.. The connection is not ready to be closed--it's still being used to populate the DataTable(s).
2.. The Connection is open when the Fill method is executed.
Let's assume that #2 is not an issue. However, if there is something that's preventing the Fill from finishing, it could certainly cause the problem you're seeing. Your queries seem pretty simple but since you asked for TOP 1 why did you ask for an ORDER BY? Check the number of SQL Server connections with PerfMon, I expect that the code is opening a new connection each time. Turn on the Profiler and see what's getting sent.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

StuartV said:
I'm getting the error message so many others have reported. I.e.:

"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've read many posts on responses on the subject. The responses all seem to boil down to "you're leaking connections somehow." If I have read the documentation correctly, then I don't believe that is my problem, and here is what I've done to prove it to myself.

I'm using Windows 2000 Server, SQL Server 2000 Developer's Edition, and Visual Studio.NET 2003. I'm completely up to date on all service packs for everything (except possibly if there's something newer than SP3 for SQL Server).

- I created a new blank Web Form. I used drag-and-drop in the Designer to add a sqlDataAdapter. In there, I selected for it to generate SQL statements, and I constructed a select statement that does "select top 1 * from requests where system_id>@system_id order by system_id". This process resulted in automatic generation of a sqlConnection object that also appears in Designer.

- The connection is to a local SQL Server database.

- I then used drag-and-drop to add a DataSet object to my form. I chose a typed dataset that was already part of my project, that was also generated by VS.NET.

- I used drag-and-drop to add 4 server controls to my form - 3 labels and a button. The labels are databound to 3 columns in the dataset (again, all done visually - no coding so far). I created NO server-side handler for the button, so when you click it it just runs the Page_Load method and returns.

- In the Page_Load method, I put in this code:

if ( !IsPostBack )
sqlDataAdapter1.SelectCommand.Parameters["@system_id"].Value = 0;
else
sqlDataAdapter1.SelectCommand.Parameters["@system_id"].Value = lblSystemId.Text;
sqlDataAdapter1.Fill( dsRequestTest1 );
DataBind();

The effect of this code is simply to have the page display the first record in the table when the page is first displayed. Each time I click the button on the page, it displays the next record and subsequent records from the database.

Those 6 lines of code are the ONLY code I have added to this page.

- When I run this page, I can click the button on the page 99 times and see the first 100 records in the table. When I click it the 100th time, I get the error.

- I edited my sqlConnection object's connection string and added "max pool size=3". Now when I run it, I see the first 3 records, then get the error.

- My understanding is that the sqlDataAdapter.Fill method opens and closes the sqlConnection itself, so I don't have to do an explicit Close. However, I tried it just to see what would happen. After the call to Fill(), I added these two lines:

sqlConnection1.Close();
sqlDataAdapter1.SelectCommand.Connection.Close();

I still get the exact same behavior.

- Also, I have tried using PerfMon to monitor the pooled connections in use, under the .NET CLR Data section of counters. It never shows any in use. The connection pool count goes from 0 to 1, but the pooled and unpooled connections in use stays at 0. This is for _global and all other instances of the counters.

Help?!
Click to expand...
 
Our investigation revealed that the .NET SQLConnection Connection Pool
is - at Connection Timeout (Default 4 hours) - doing something that
seems to be statistical analysis - and during this analysis - no more
connections can be retrieved from the pool. In our case we are using
a connection from the pool 5 times a second. When the pool
statistical analysis runs - it is analysing 5 * 60 * 60 * 4 (Hours)
worth of statistical data. This process takes about 20 minutes. 20
minutes where the pool is unavailable !!! Solution - make the
timeout about 30 seconds so that the analysis has a lot less data to
analyse - and then the lag created by this analysis is one hellova
lot shorter and has virtually no impact.

i.e. Add ";Connection Lifetime=30" to your connection string. Timeout
is now 30 seconds.
 
I don't think it performs such a statistical analysis. It looks rather like
you are exhausting available connections and minimizing the pool lifetime
allows to workaround the problem. If no connection are available it waits
(likely not that much) in case a connedction would be soon freed.

In particular it looks like you are using new connections at a very high
frequency. Are they properly returned to the pool ? How many connections are
used ? You may want to add some code when DEBUG is on to track possible
unreleased connections...

Patrice
 
If you browse this newsgroup, you'll see there are many threads that talk
about the connection pool. "Connection Lifetime" is used for clustered
servers. It does not affect the length of time that a connection remains in
the pool after it is closed for typical server rigs. That time is a random
number (4-8 minutes) of minutes. It won't be able to be altered until V2.0
of the Framework ships.

I also expect your problem is caused by not getting your connections closed
in time.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Davem,
For one of the best descriptions on how connection pooling works internally
with SqlClient take a look at this excellent article by Guy Smith Ferrier:
http://www.sys-con.com/dotnet/article.cfm?id=483

The connection timeout algorithm that we use is perfectly described in this
article. I suspect that the "statistical analysis" theory that is making the
pool unavailable is that you are running into issues where you are not
closing the connection properly and you are relying on the .net garbage
collector to work to be able to reuse connections. Take a look at the
following thread for more information on why not closing connection is bad
and how you can be leaking connections even if your code calls close but not
on a finalizer.
http://groups.google.com/groups?hl=...XHGRBuDEHA.3568%40tk2msftngp13.phx.gbl&rnum=1

Hope this helps
 
I've read all your comments and the articles.
Thank you very much for your input.

However, the facts still remain:
Setting the connection pool timeout to 30 seconds solved the "20
minute pool not available" problem every 4 hours.

Patrice:
The code we use is clean - at the current rate of 3-5 connections a
second the pool size is never bigger than 5 (Except at startup of
where the pool size starts of at a higher number and within an hour
or so stabilizes at 5 connections.). It is almost impossible for me
to say how many connections are running simultaneously except for me
to say that there is currently never more than 4 or 5 (Multithreaded
Windows NT Service.). As more data-feeds are added - we expect to
peak at 50 data feeds where currently there are 8 - the number of
concurrent connections will increase.

William (Bill) Vaughn:
This random time for a cleanup - 4-8 minutes ... we have found this to
be true in all of our other cases. In this instance, when the service
is started, the start off at 15 connections and you can see some the
number of connections changing in the area of 4 - 8 minutes, so all
is going to plan. They stabilize at 5 connections. Then - about 4
hours later, the queue is locked up. Any request for a connection
from the queue at this point eventually returns with the timeout
error. About 20 minutes later the pool works fine again.

Angel Saenz-Badillos:
Awesome link - thanks a gazillion. My theory is that our excessive use
of the pool is actually preventing the timer callback from getting
processor time to run properly. The callback ends up sitting
partially executed in some stack and that stack builds up until a
peak is reached where the queue is given priority to enable it to
finish it's task's. This is what locks up the pool for 20 minutes.
The connection lifetime setting should have absolutely no effect,
but, maybe this has some effect on another level that involves
thread/queue priority.

Summary:
I tried setting the connection lifetime as part of a "try everything"
excercise, and it worked. It should have had no effect, but it worked
and is still working. Nothing else I tried had any useful effect and I
spent 2 full days on this.

PS - The live server = quad processors, 3 GB RAM, SQL Server 2000 STD
(Boosted Priority) + .NET 1.1. The test server = 1 slooooow
processor, 512 MB RAM, SQL Server 2000 STD (Boosted Priority) + .NET
1.1. The 4 hour effect was the same on both machines despite the huge
difference between the hardware's.
 
Just a shot in the dark: did you try the same scenario but with SQL Server
priority boost disabled?

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.
 
I 've found a solution to this problem in VS2003

I have had this exception and set Enable Sql Debugging = False
 and the problem got solved

The Problem
1- I am running VS2003 and sqlserver200, Windows Forms
2- Exception: "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."
3- I just run about 100 select statements and I closed the open connections immidiately.

The Solution: Set this property of project
Enable Sql Debugging = False

 
Last edited:
Back
Top