You must Sign In to post a response.

15 Jun 2016 05:20

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	5

How To Resolve-All Pooled Connections were in use and Max Pool size was reached Error.

Hi Developers ,
In Two of my projects
When i am try to Insert,Update a Record in my Website , it throws the following Error Messsage on Local and Live .
that is

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.

One project the problem has been resolved successfully by me.
now it is working fine on local and live.
My using code:

<add name="labmate_constr" connectionString="Data Source=198.11.11.11;Initial Catalog=labmate;
Min Pool Size=0;Connection Lifetime=0; Max Pool Size=1500;
Connection Timeout=30;Pooling=true;User ID=tr_labmate;Password=yyyyy" providerName="System.Data.SqlClient" />

But another one i can't able to resolve and run it.

Am used the same code but still i am get the error message.
So give me some suggestion to resolve this error.

Thanks with
Paul.S

#766477

15 Jun 2016 07:11

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	4

Hi,

Usually we got this type of errors, when we try to get database data into our application, that time if it's take more than Connection timeout we will get this type of error, since you are facing same issue with different project and you successfully resolve that, please use the same steps to resolve this one too.. could you please confirm how many records are you trying to fetch.

My suggestion to resolve this is reduce the loading time by applying some code optimization techniques, that will improve performance,.

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#766482

15 Jun 2016 21:27

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	1

Am try to Fetch more than 20 records Mr.Naveen, Join of 2 Tables.
in my another project am also try to fetch more than 25 records but it is working properly.
Thank you Mr.Naveen

#766483

15 Jun 2016 22:22

Joined:	21/11/2010
Level:	Bronze
Points:	81

Points:	4

In most cases connection pooling problems are related to "connection leaks." Your application probably doesn't close its database connections correctly and consistently. When you leave connections open, they remain blocked until the .NET garbage collector closes them for you by calling their Finalize() method.

You want to make sure that you are really closing the connection. The correct way would be this:

var connection = new SqlConnection(ConnectionString);
try
{
connection.Open();
someCall (connection);
}
finally
{
connection.Close();
}
OR
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
someCall(connection);
}
also
Check against any long running queries in your database.
Increasing your pool size will only make your web app live a little longer (and probably get a lot slower)
You can use SQL server profiler and filter on duration / reads to see which query need optimization.
I also see you're probably keeping a global connection?
check your query with SQL server profiler and see if you can optimize it. Having a slow query with many requests in a web app can give these timeouts too.

#766486

15 Jun 2016 23:37

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	4

Hi Paul,

Since you got minimal records from database then you need not worry about in this point, after Googling the same issue I found the problem is closing connections, you are opening connection and perform some action but you didn't close the connection or longer time you are keeping the connection state is open that time open connection is happen in one place, perform action in another place and closing connection in another place, this is happen.

To Overcome the above issue, you need to close the connection as soon as possible, my suggestion is always use "using" statement this will close the connection automatically once complete the action.

Refer below link for more details, https://blogs.msdn.microsoft.com/spike/2008/08/25/timeout-expired-the-timeout-period-elapsed-prior-to-obtaining-a-connection-from-the-pool/

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#766496

16 Jun 2016 01:33

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	3

Hi
Paul

try this code



SqlConnection ObjConn = new SqlConnection("Connection String");
            SqlCommand ObjCmd = new SqlCommand("Command String", ObjConn);           
            ObjCmd.CommandTimeout = 0;
            ObjCmd.ExecuteNonQuery();

OR
string connstring="Data Source=localhost;User ID=sa; pwd=password; Initial catalog=Test;connect timeout=900;

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

#766497

16 Jun 2016 01:37

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	1

Thanks Mr.Naveen and Mr.Kedar,

My Connection Query

public class Connection
{
public static string conStr = ConfigurationManager.ConnectionStrings["Boards_constr"].ToString();

public static SqlConnection DBConn()
{
SqlConnection con = new SqlConnection(conStr);
if (con.State == ConnectionState.Open)
con.Close();
con.Open();
return con;
}
}

There is any mistake in my Connection Query . . ?

Thanks With
Paul.S

#766500

16 Jun 2016 02:38

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	4

Hi Paul,

As per your code logic, if connection is open then you are closing the connection and open again,

for example for one action you open connection, and you perform action after complete the action you should close the connection, but in your case when you perform 2nd action that time if connection is open then only you are closing it, i guess this is the problem because of this you will get this error. I suggest you to use "using" statement or close the connection immediately after complete your action.

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#766571

20 Jun 2016 23:28

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	4

it happens due to some of the data objects are still open, you need to close all ado.net objects that you used, example, datareader, datset, dataadapter, dataconnection
if you do not close them it may leads to an timeout error
In most cases connection pooling problems are related to "connection leaks." Your application probably doesn't close its database connections correctly and consistently. When you leave connections open, they remain blocked until the .NET garbage collector closes them for you by calling their Finalize() method.
You want to make sure that you are really closing the connection. For example the following code will cause a connection leak, if the code between .Open and Close throws an exception
correct way to close connection is as below


var connection = new SqlConnection(ConnectionString);
try
{
     connection.Open();
     someCall (connection);
}
finally
{
     connection.Close();                
}

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

#766584

21 Jun 2016 00:36

Joined:	05/06/2009
Level:	Gold
Points:	10245

Points:	3

You can close all the connection before open the new connection. for that you can check the "State" property of the connection.


if (mySQLcon.State == ConnectionState.Open)
{
    mySQLcon.Close();
}
mySQLcon.Open();

The above code will close the previous connection before open the new connection.

By Nathan
Direction is important than speed

#766708

23 Jun 2016 02:59

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	1

Thanks for all your reply friends , thanks a lot.

i have rectify this error .

My Mistake is ;

public void BindSlot()
{
string slot = "select * from tbl_fr where enquiryid='" + EnquiryID.ToString() + "' and fr_id='" + FRID.ToString() + "'";
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(slot, con);
sda.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{

txtNoofSlots.Text = ds.Tables[0].Rows[0]["noofslots"].ToString();
txtSlot_if_any_in_mm.Text = ds.Tables[0].Rows[0]["slot_if_any_in_mm"].ToString();
if (txtSlot_if_any_in_mm.Text == "YES" && txtNoofSlots.Text != "")
{
Label30.Visible = true;
txtNoofSlots.Visible = true;
BindSlot(); ==> Error
}
}
else
if (txtSlot_if_any_in_mm.SelectedValue == "NO")
{
Label30.Visible = false;
txtNoofSlots.Visible = false;
}
}

i have called the function Named BindSlot() with in the Same Function.
Put break point and i am find this error.

Thanks with
Paul.S

#766711

23 Jun 2016 03:04

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	3

Hi Paul,

In one method you are calling the same method without parameters why it is required?

Is there any specific reason for the same, where did you open the connection their?

I guess the problem with the closing connections and some other objects, please recheck your logic why are you implement the same method inside a method.

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#766864

28 Jun 2016 10:10

Joined:	20/03/2013
Level:	Gold
Points:	4176

Points:	2

write finally block to close and as well as dispose the connections


try
{
}
catch
{

}
finally
{
 con.close();
con.dispose();
}

.

finally block will be executed whether error is thrown or not.

SRI RAMA PHANI BHUSHAN KAMBHAMPATI

Submit New Thread

Return to Return to Discussion Forum

How To Resolve-All Pooled Connections were in use and Max Pool size was reached Error.

About dotnetspider.com

Quick Links

General

Services