SqlClient Data Provider too many processes per user

Posted on 2009-04-01
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 100 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?
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 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