You must Sign In to post a response.
  • Category: ASP.NET

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


Sign In to post your comments