?
Solved

SqlClient Data Provider too many processes per user

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

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?
0
Comment
Question by:conrad2010
[X]
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
9 Comments
 
LVL 15

Expert Comment

by:oobayly
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);
try{
  conn.Open();
 
  // Do stuff
}finally{
  // Make sure the connection is always closed so it's
  // returned to the connection pool
  conn.Dispose();
}

Open in new window

0
 

Author Comment

by:conrad2010
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...
0
 
LVL 3

Assisted Solution

by:OneMHz
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?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 15

Expert Comment

by:oobayly
ID: 24047699
@OneMHz
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.
0
 

Author Comment

by:conrad2010
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...
0
 
LVL 15

Expert Comment

by:oobayly
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?
0
 

Author Comment

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

Accepted Solution

by:
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);
        writer.Close();
      });

Open in new window

0
 

Author Closing Comment

by:conrad2010
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...
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to setup several different housekeeping processes for a 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.
Suggested Courses

762 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