Have you seen following error? timeout-expired-the-timeout-period-elapsed-prior-to-obtaining-a-connection-from-the-pool-this-may-have-occured-because-all-pooled-connections-were-in-use-and-max-pool-size-was-reached
Then this post is to solve it!
Solution:
As per the error your code has not closed the opened SqlConnection properly. For example
SqlConnection conn = new SqlConnection(
myConnectionString);
conn.Open();
doSomething(); /* If some error occurs here — Next line will not get called and it will leave connection open */
conn.Close(); Solution:
1.
SqlConnection conn = new SqlConnection(myConnectionString);
try
{
conn.Open();
doSomething(conn);
}
finally
{
conn.Close(); // This line will get called in any case — success/failure
}
So, open your solution in Visual Studio and search in entire solution for all open connections and for all the code implement above suggested solution.
Just a note : If you have written Data Access layer code in code behind file then you are in trouble here. You have to do changes at N number of places. If you would have created separate Data Access layer (Technically Class Library) and Method to do DB operation then your task would have been easy enough!
2) You can raise the connection pool size in the connection string. For example, you can add “Max Pool Size=100” to your connection string to increase the pool size to 100.
Implement above solutions. You should not see any issues any more.
Y’day I came across with nice issue. Basically there is one Web Application developed using ASP.NET and deployed on IIS Server. Now, this application will be accessed by more than 2-3 people from Local area network. So, far so good.
This application uses Session and here issue comes — For all users across different machines were getting access of different persons session data — which should not be the case. Because as per theory “session” is unique for each user. But my dear friend theory is theory in real life you have to face lots of challenges like this. 🙂
Solution:
So, I jumped in to this issue and first tried to understand what is going on [The basic stuff — which I always do!].
To quick check the Session ID and other Session related information. I decided to code one page which will print my session info on page and it should show me some direction. I opened my favorite tool — Visual Studio and added one page using single file model — which I can deploy easily on server.
The code looks like below:
<%@ Page Language=”C#” AutoEventWireup=”true” %>
<%@ Import Namespace=”System” %>
<!–DOCTYPE html PUBLIC “/-/W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
xmlns=”http://www.w3.org/1999/xhtml”>
<script language=”C#” runat=”server”>
// new line
private const string breakLine = ”
“;
// Strong Start
private const string strongStart = ““;
// Strong End
private const string strongEnd = “”;
private const string sessionTimeKey = “SessionTime”;
protected void Page_Load(object sender, EventArgs e)
{
// generate string with all required information
StringBuilder sessionInformation = new StringBuilder();
if (Session[sessionTimeKey] == null)
// Current Time
Session[sessionTimeKey] = DateTime.Now;
// IsCookieless
sessionInformation.Append(strongStart);
sessionInformation.Append(“IsCookieless : “);
sessionInformation.Append(strongEnd);
sessionInformation.Append(Session.IsCookieless.ToString());
sessionInformation.Append(breakLine);
// IsNewSession
sessionInformation.Append(strongStart);
sessionInformation.Append(“IsNewSession : “);
sessionInformation.Append(strongEnd);
sessionInformation.Append(Session.IsNewSession.ToString());
sessionInformation.Append(breakLine);
// Session.Keys.Count
sessionInformation.Append(strongStart);
sessionInformation.Append(” Total Keys Count : “);
sessionInformation.Append(strongEnd);
sessionInformation.Append(Session.Keys.Count.ToString());
sessionInformation.Append(breakLine);
// Mode
sessionInformation.Append(strongStart);
sessionInformation.Append(” Session Mode : “);
sessionInformation.Append(strongEnd);
sessionInformation.Append(Session.Mode.ToString());
sessionInformation.Append(breakLine);
// SessionID
sessionInformation.Append(strongStart);
sessionInformation.Append(” SessionID : “);
sessionInformation.Append(strongEnd);
sessionInformation.Append(Session.SessionID);
sessionInformation.Append(breakLine);
// Timeout
sessionInformation.Append(strongStart);
sessionInformation.Append(” Timeout : “);
sessionInformation.Append(strongEnd);
sessionInformation.Append(Session.Timeout.ToString());
sessionInformation.Append(breakLine);
// Session Value
sessionInformation.Append(strongStart);
sessionInformation.Append(” Session Value(DateTime) : “);
sessionInformation.Append(strongEnd);
sessionInformation.Append(Convert.ToString(Session[sessionTimeKey]));
sessionInformation.Append(breakLine);
// SERVER_NAME
sessionInformation.Append(strongStart);
sessionInformation.Append(” SERVER_NAME : “);
sessionInformation.Append(strongEnd);
sessionInformation.Append(Request.ServerVariables[“SERVER_NAME”]);
sessionInformation.Append(breakLine);
// SERVER_PORT
sessionInformation.Append(strongStart);
sessionInformation.Append(” SERVER_PORT : “);
sessionInformation.Append(strongEnd);
sessionInformation.Append(Request.ServerVariables[“SERVER_PORT”]);
sessionInformation.Append(breakLine);
// LOCAL_ADDR
sessionInformation.Append(strongStart);
sessionInformation.Append(” LOCAL_ADDR : “);
sessionInformation.Append(strongEnd);
sessionInformation.Append(Request.ServerVariables[“LOCAL_ADDR”]);
sessionInformation.Append(breakLine);
// REMOTE_ADDR
sessionInformation.Append(strongStart);
sessionInformation.Append(” REMOTE_ADDR : “);
sessionInformation.Append(strongEnd);
sessionInformation.Append(Request.ServerVariables[“REMOTE_ADDR”]);
sessionInformation.Append(breakLine);
// REMOTE_HOST
sessionInformation.Append(strongStart);
sessionInformation.Append(” REMOTE_HOST : “);
sessionInformation.Append(strongEnd);
sessionInformation.Append(Request.ServerVariables[“REMOTE_HOST”]);
sessionInformation.Append(breakLine);
// SERVER_NAME
sessionInformation.Append(strongStart);
sessionInformation.Append(” SERVER_NAME : “);
sessionInformation.Append(strongEnd);
sessionInformation.Append(Request.ServerVariables[“SERVER_NAME”]);
sessionInformation.Append(breakLine);
Response.Write(sessionInformation.ToString());
sessionInformation.Append(breakLine);
Response.Write(“———-SESSION CONTENT——————-“);
Response.Write(breakLine);
foreach (string item in Session.Contents)
{
if (Session[item] != null)
{
Response.Write(string.Format(“Key :{0} – Value : {1}”,
item,Convert.ToString(Session[item])));
Response.Write(breakLine);
}
}
}
protected void btnRefresh_Click(object sender, EventArgs e)
{
Response.Redirect(Request.Url.ToString());
}
</script>
<head runat=”server”>
<title>Session Check Page</title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<asp:Button ID=”btnRefresh” runat=”server” Text=”Refresh” OnClick=”btnRefresh_Click” />
</div>
<br />
</form>
</body>
</html>
Basically, this page is my favorite page. Before a year using this page’s code only. I fixed one Live issue [Sticky Session was of on Load balancer on our web farm environment]. If you see the code of page it is quite simple. But very useful!
What I check there is Session ID, LOCAL Address and Session value which I store on page load’s not postback only. And it has one nice button called “Refresh” which redirects on same page.
We deployed this page on Server and started accessing it from different – different machines. And we started clicking on “Refresh” button which shown us few strange thing as below:
1. SessionID was changing on each Refresh button’s click – This should not be the case. Because Session ID will be unique till session expires or user closes the browser.
2. Session’s value was also changing on each refresh.
3. REMOTE_ADDR was coming same on 2-3 machines.
Now, we started removing possibilities one by one.
For issue#1 — We checked Web.Config and found that StateNetworkTimeout property has been assigned, frankly it should not cause an issue. But we thought better to remove it. So, we removed it.
#2 ,#3 – Then we realized that we are using Proxy on our LAN and REMOTE_ADDR’s IP address was IP Address of proxy server.
So, we found that Proxy is causing us an issue. To resolve it we removed proxy server’s setting from Internet explorer’s settings for few machines from which we need to access this application.
Isn’t it simple?
Happy Coding! 🙂
I still remember my 1,00,000 hits blog which I wrote on 23rd August 2011. And today Just saw that we crossed 1,50,000 blog hits and this not an end this is just beginning because I know that you — my blog readers – will keep visiting, reading, and sharing my blog. Thanks to each and every visitor for contributing in to this count so all credit goes to you! — Thank you again!
1,50,000 Hits
I remember that I’ve started blogging from December 2007 and till today I’m learning how to write a good blog — yeah agree that each and every day I’ve learnt a lot!
One thing which motivates me a most to keep my blog alive and writing tirelessly, Is people’s comments. When I check the comments people says “Oh man you saved my day”, “Thank you”. Frankly speaking it stimulates a lot. It absorbs all my day’s tiredness and inspires me to write one new blog 🙂 [So, If you like anything — not only on my blog or virtual world. I am talking about all aspects. Then don’t forget to appreciate it!]. Some comments I would like to share with you:
THANK YOU THANK YOU! Days of Tracking down solution NONE WORKED!!! EXCEPT
YOURS! This is was a great Help. Thank you Very Much. Even Microsofts sight, did not have this solution for this probolem….like that is a surprise!
Thank god there are people like you. And of course all you others that came here as me…
Thnaks
/Ralf
Thanks a lot it worked for me…
thank you ,it’s helped me alot
wow…life is truely an enjoyable mystery sometimes(with a few bumps/challenges here and there). I got your link from an email one of our developers sent another programmer. I saw what the conversation was about so I followed the link (I also do some programming). After reading a few of your posts and your comments, I was truely impressed with your spirit. Aside from programming, it was nice to stumble upon your site and to walk away with a few tid-bits of inspiration…code, life. In this world today, we all need to remind each other of gratitude, thankfulness, and yes, happiness. Thank you my friend for letting your heart shine.
Thanks for the tip…
really helped me.I also want to do a similar thing. and it works fine
Thank you, this was driving me nuts and changing to a dataset finally gotit working. Awesome!
You, my friend, are a god-send. This is exactly the fix I needed. I’ve spent a couple days trying to get my vbs files to work and was about to give up and rebuild this XP machine.
Thank you.
Thanks Kiran..!
The solution rele worked..
After searching a lot on the net finally got the solution..
Thanks to your Blog..
You Rock Man..! Cheers..!
Worked like a champ.
May Allah give u peace…
nice..it helps me alot…
HADAKALLAH
Super Duper Work Superman…!
Thanks kiranpatils for such lovely blogs you write…
They are a great source of knowledge as well as inspiration to me and I surely know to many others….
Awesome! Saved me some time here – thanks!
Awesome, tried this 100% working. Thank you heaps.
Thank you very much for this article..I got a clear idea of web service
This is our MAN OF MICROSOFT..
COOL WORK OVER THE WORDPRESS BROTHER. HAPPY TO SEE YOUR “HAPPY TO HELP” ATTITUDE.. AS I ALWAYS SAY HE IS “MAN OF MICROSOFT”…. Keep It Up Bro… He deserve to be MVP..
I want you all people to encourage his blogging and appreciate his affords to share his excellent and oustanding knowledge which he has gained by hard work. ( 😉 i knw the way he worked for excellancy, productivity and knowledge gaining for .Net Framework ) if his answer really help you out (which i strongly believe his solution will 100000000% work). You can keep close your eyes and do what he says 😉 and there you are with your all answers.
Finally I would again say “MAN OF MICROSOFT” and one of my BESTEST buddy ever. [though hardly get to talk and meet]
HOPE TO SEE YOU SOOOOOOOOON ON THE LIST OF “MVPs”….(You very well know how long its been I am waiting to see you on MVPs List so please make it faster brother)
ALL THE VERY BEST TO YOU “MAN OF MICROSOFT”…..
Thanks for this post. It has been driving me nuts to figure how to make “LinksTreeView.FindNode(txtPath.Text);” work with populateondemand set to true.
And many more.. — If I share all of them then this blog post will be too huge!
Here’s the Stats Summary which I would like to share with you:
Quick Facts
Total Posts : 131
Total Comments : 328
Total Categories : 44
Total Tags : 74
Total Visits 2007 : 438
Total Visits 2008 : 23,606
Total Visits 2009 : 46,991
Total Visits 2010 : 47,088
Total Visits 2011(till date) : 31,996
Average Visits per Day : 190
Thanks to all who inspired and appreciated my work – Yes I am saying thanks to you – My friends,readers and daily visitors!