none
All Pooled Connections use Max Pool Size RRS feed

  • Question

  •  

    Hello Experts,

    I Use Following Connection Setting inside my code:

        SqlConnection cn = new SqlConnection();

        public db()
        {
            string cnn = ConfigurationManager.ConnectionStrings["Mycon"].ConnectionString;
            this.cn.ConnectionString = cnn;
        }

        private SqlConnection connection()
        {
            
            
            
          if (cn.State == ConnectionState.Open)
            {
                cn.Close();
            }
            cn.Open();
            return cn;
        }

    And my web.config Connection String is as follows:

    <add name="MyCon" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=teagarden;User Id=sa; Password=1234

    I am using a loop as follows :

    foreach (GridViewRow row1 in gvCustomers.Rows)
            {
                GridView gv = ((GridView)row1.FindControl("gvOrders"));
                foreach (GridViewRow row in gv.Rows)
                {
                    Label aucDetId = ((Label)row.FindControl("Label1"));
                    Label lotNo = ((Label)row.FindControl("Label2"));
                    TextBox price = ((TextBox)row.FindControl("txtTopPrice"));
                    DropDownList bidBy = ((DropDownList)row.FindControl("drpBidby"));

                    cls.AucDetId = Convert.ToInt32(aucDetId.Text);
                    cls.Lotno = Int32.Parse(lotNo.Text);
                    cls.Price = float.Parse(price.Text);
                    cls.BidDate = DateTime.Now;
                    cls.BidBy_UserID = Convert.ToInt32(bidBy.SelectedValue.ToString()); ;
                    clsad.Id = Int32.Parse(aucDetId.Text);
                    if (bidBy.SelectedIndex > 0)
                    {
                        dal.Insert(cls);
                    }
                }

    Now When I try to Save my data using above code, My program returns following 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 don't exactly know why it happens.

    Pls help me asap.

    Thanks in advace

    Mojam, WSS

    Saturday, January 29, 2011 5:56 AM

Answers

  • Hello Mojam,

     

    Welcome to the MSDN Forum and thank you for posting here.

    Here are some solutions that you can try to solve the problem:

    1) Check your application to make sure all database connections are closed when it is not needed.  ASP.NET is supposed to have garbage collector to reclaim unused resource.  However, on a busy site, it is likely that the connection pool will run out of connections before garbage collection kicks in.

    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.

    I think the code of you is not good.

    if (cn.State == ConnectionState.Open)
            {
                cn.Close();
            }
            cn.Open();
            return cn;
     See also:

    http://blogs.msdn.com/b/tolong/archive/2006/11/21/max-pool-size-was-reached.aspx

    http://blogs.msdn.com/b/angelsb/archive/2004/08/25/220333.aspx

    I hope they can help you.

     

    have a good day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, January 31, 2011 6:22 AM
    Moderator
  • Where you have implemented this method " private SqlConnection connection()" ?. If it is with in a class, and connection object is not marked as static,   if (cn.State == ConnectionState.Open) this condition will never meet. because each time class instantiate new connection object will be created. So you will end up with lot of unused opened connection.

    use 'using' block wherever you need a connection and write all DB access operation within the block. If you ever used Connection.Open( ) always close it with Connection.Close() .


    Monday, January 31, 2011 6:47 AM

All replies

  • Hello Mojam,

     

    Welcome to the MSDN Forum and thank you for posting here.

    Here are some solutions that you can try to solve the problem:

    1) Check your application to make sure all database connections are closed when it is not needed.  ASP.NET is supposed to have garbage collector to reclaim unused resource.  However, on a busy site, it is likely that the connection pool will run out of connections before garbage collection kicks in.

    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.

    I think the code of you is not good.

    if (cn.State == ConnectionState.Open)
            {
                cn.Close();
            }
            cn.Open();
            return cn;
     See also:

    http://blogs.msdn.com/b/tolong/archive/2006/11/21/max-pool-size-was-reached.aspx

    http://blogs.msdn.com/b/angelsb/archive/2004/08/25/220333.aspx

    I hope they can help you.

     

    have a good day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, January 31, 2011 6:22 AM
    Moderator
  • Where you have implemented this method " private SqlConnection connection()" ?. If it is with in a class, and connection object is not marked as static,   if (cn.State == ConnectionState.Open) this condition will never meet. because each time class instantiate new connection object will be created. So you will end up with lot of unused opened connection.

    use 'using' block wherever you need a connection and write all DB access operation within the block. If you ever used Connection.Open( ) always close it with Connection.Close() .


    Monday, January 31, 2011 6:47 AM