Solved

SqlClient Data Provider too many processes per user

Posted on 2009-04-01
9
865 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
  • 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 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?
0
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 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);

        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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now