Link to home
Start Free TrialLog in
Avatar of JamesAnthony
JamesAnthonyFlag for Ireland

asked on

Connection Pooling

Hi All
Have this code, works etc but I am trying to fill a table with sql attached, works for 541 records exactly every time then get error listed
I dont want to increase the max pool size as doing this will affect other servers that I have to connect to


this is statement in a loop, values change accordingly
 
Call executesql(insert into pricematrixWgtNew( DescNo, Sex, Conf, Fat, Code1, KgMin, KgMax, Penback,Unit) select 1,'A','O+','5-',886,0,239.9,0,'K'")

CODE

   Sub ExecuteSQL(ByVal SQLStateMent As String)


        gCancel = False
        Dim ExecuteConn As New SqlConnection(ConnStr)
        If ExecuteConn.State = 1 Then ExecuteConn.Close()
        ExecuteConn.Open()
        Dim SQLCommand1 As New SqlCommand
        Try
            SQLCommand1.CommandType = CommandType.Text
            SQLCommand1.CommandText = SQLStateMent
            SQLCommand1.Connection = ExecuteConn
            SQLCommand1.ExecuteNonQuery()
            If ExecuteConn.State = 1 Then ExecuteConn.Close() : ExecuteConn.Dispose()
        Catch exsql As SqlException
            'MyBox(exsql.Message)
            gCancel = True
            If ExecuteConn.State = 1 Then ExecuteConn.Close() : ExecuteConn.Dispose()
        End Try
    End Sub


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.

Avatar of guvera
guvera
Flag of India image

Hi,
 

  Please check the below links.

  http://forums.asp.net/t/465979.aspx/1
  http://social.msdn.microsoft.com/Forums/en-IN/adodotnetdataproviders/thread/c57c0432-c27b-45ab-81ca-b2df76c911ef

 For your information also read this article,

 This problem occurred most probably because of connection leak. Either the connection string do not close properly or consistently.

When you intend to close your database connection, you want to make sure that you are really closing it. The following code looks fine yet causes a connection leak:

     SqlConnection conn = new SqlConnection(myConnectionString);

      conn.Open();

      doSomething();

      conn.Close();    
           

If doSomething() throws an exception - conn will never get explicitly closed. Here is how this can be corrected:


     SqlConnection conn = new SqlConnection(myConnectionString);

      try

      {

            conn.Open();

            doSomething(conn);

      }

      finally

     {

            conn.Close();                

      }


When returning a connection from a class method - make sure you cache it locally and call its Close method. The following code will leak a connection:


     OleDbCommand cmd new OleDbCommand(myUpdateQuery, getConnection());

      intres = cmd.ExecuteNonQuery();

     getConnection().Close(); // The connection returned from the first call to getConnection() is not being closed.

Instead of closing your connection, this line creates a new one and tries to close it.

 
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.

Thanks
Guvera
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JamesAnthony

ASKER

Short and sweet, got the job done
thanks
Glad to help :-)