SqlClient Data Provider too many processes per user

Posted on 2009-04-01
Medium Priority
Last Modified: 2012-05-06
ASP.NET (2.0 and 3.5)
VS2008 (and VS2005)
SQL Server 2000 Standard

When running an aspx website connected to a SQL database (and after 3 page refreshes) I am getting this 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 check the sysprocesses using this query:
SELECT     program_name, COUNT(*) AS Expr1
FROM         master.dbo.sysprocesses
GROUP BY program_name
ORDER BY COUNT(*) DESC            

this process ".Net SqlClient Data Provider" comes up as running more than 100 processes after refreshing the aspx page that makes the database connection and I am the only user on this system.

to prevent SQL Server timeout I add this to the connect string: "Min Pool Size=5;Max Pool Size=500;Connect Timeout=2;" and I can run without timing out.

Now the kicker: the same website on a different server runs only 1 ".Net SqlClient Data Provider" no matter how many page refreshes (which tells me the problem is not in the asp code but on some server configuration which on first checkup look the same).

Where do I need to look for this problem?
Question by:conrad2010
  • 4
  • 4
LVL 15

Expert Comment

ID: 24044794
Sounds like you're not closing connections when you're done with them. If you don't explicitly close a connecion you opened, it will not be returned to the connection pool.

I always do the following, so my connections are guaranteed to be closed after I use them.
SqlConnection conn = new SqlConnection(connectionString);
  // Do stuff
  // Make sure the connection is always closed so it's
  // returned to the connection pool

Open in new window


Author Comment

ID: 24044824
Hi oobayly,

that was my first thought also, but the identical same code runs flawlessly on another server (only 1 connection) so it looks like the problem is not in the software...

Assisted Solution

OneMHz earned 400 total points
ID: 24046008
Try calling conn.Close() instead of dispose or try a using block. (shot in the dark really)

using(SqlConnection conn = new SqlConnection(connectionString))
    //Do Stuff

You may also have an issue with an unclosed DataReader.  Try making sure to close those and consider cases where you're doing a Response.Redirect, or Server.Transfer or stopping the page processing early for any other reason.

As for why it works on one but not the other, are they the same version of IIS?  Are you sure they're configured to run the same version of .Net?  Might you have missed a stray "Pooling=false" in the config or connection string somewhere?  Also, with a 2 second connect timeout, could it be that your having problems on a dev machine that's far away from the DB, where the working server has a better, more direct connection?  Have you tried setting that higher to see if it changes anything?
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 15

Expert Comment

ID: 24047699
Calling conn.Dispose() calls conn.Close() on the connection. That's why the using statement will also close the connection when it leaves the block as it calls Dispose()

You could try using a network monitoring tool like Wireshark . That way you you could check if you're seeing a lot of TCP resets, out of sequence packets & retransmissions. SQL Server runs on TCP 1433 by default.

Author Comment

ID: 24051557
The setup is identical (same IIS, same .NET, same SQL, same code, same web.config) All applications are configured as default.

If I take out the pooling info from the connect string (no 2 sec timeout) this is what happens:

server 1: loading a cretain page opens 33 connections (".Net SqlClient Data Provider"), every refresh of the page opens 33 more (and so on until they timeout)

server 2: loading that same page opens 1 connection, refreshing the page doesn't add any connections (the only time a second connection opes is when another user opens that page)

the only lead I have is this: in the aspx pages, the connect string is held in a session variable (I know, bad stuff, but this is what it is right now). What's even worse, this one page opens about 30 of them to load (fragmentation). All bad stuff, but that's beside the question since on server 2 everything runs as expected, i.e. 1 pooled connection.

So the question could be: what causes the session variable to expire on server 1 (if that's what it is). If the sessions are not stored, then they are recreated each time they are called for.

Just thinking out loud here...
LVL 15

Expert Comment

ID: 24052762
As nasty as keeping the connectionstring in the session variable, I'd be immensly surprised if that was the cause as:
  • If the session expires, the connectionstring will be added back in when the new session is created (In the global.asax I imagine)
  • The session shouldn't expire half way through generating a page.
Is it possible to hook the StateChange event for a couple of the connections on the page and get them to write to a text file, just so you can see if if they think they're being closed, but aren't (I'd be immensely surprised if that was the case).
There's no wacky conditional code that runs in the page depending on what host it's being run on is there?

Author Comment

ID: 24053870
do you have an example of how to use StateChange in the context you suggest? I have no experience with that...
LVL 15

Accepted Solution

oobayly earned 1600 total points
ID: 24054062
You can hook the handler anonymously for several of your 30 connections <grin>. Just change the text in methodName so you can know which connection wrote which entry to the log.
      conn.StateChange += new StateChangeEventHandler(delegate(object sender, StateChangeEventArgs e) {
        string methodName = "Conn1";
        string logFile = @"c:\log.txt";
        System.IO.StreamWriter writer = new System.IO.StreamWriter(
          new System.IO.FileStream(logFile, System.IO.FileMode.Append, System.IO.FileAccess.ReadWrite));
        writer.WriteLine("{0}\t{1:HH:mm:ss.fff}\t{2}->{3}", methodName, DateTime.Now, e.OriginalState, e.CurrentState);

Open in new window


Author Closing Comment

ID: 31565577
I'm closing out the issue since this code is way too fragmented... I appreciate the input and maybe I'll reopen it again later...

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question