• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 906
  • Last Modified:

SqlClient Data Provider too many processes per user

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?
  • 4
  • 4
2 Solutions
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

conrad2010Author Commented:
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...
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?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.
conrad2010Author Commented:
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...
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?
conrad2010Author Commented:
do you have an example of how to use StateChange in the context you suggest? I have no experience with that...
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

conrad2010Author Commented:
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...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now