How to Solve Max Connection Pool Error

Question

I have a application in asp net 3 5 and Database is Sql server 2005    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     Some time this error occured How to solve this error     I try SqlConnection ClearAllPools    but this also not working   SqlCommand cmdToExecute   new SqlCommand                cmdToExecute CommandText    dbo  sp user listing                cmdToExecute CommandType   CommandType StoredProcedure              DataTable toReturn   new DataTable  courier user listing                SqlDataAdapter adapter   new SqlDataAdapter cmdToExecute                   Use base class  connection object             cmdToExecute Connection   sqMainConnection               try                               cmdToExecute Parameters Add new SqlParameter   suser name   SqlDbType VarChar  250  ParameterDirection Input  true  0  0      DataRowVersion Proposed   user name                      if  blnMainConnectionIsCreatedLocal                                           Open connection                      sqMainConnection Open                                      else                                       if  CPMainConnectionProvider IsTransactionPending                                                cmdToExecute Transaction   CPMainConnectionProvider CurrentTransaction                                                              Execute query                  adapter Fill toReturn                   i32ErrorCode    Int32 cmdToExecute Parameters   iErrorCode   Value                   if  i32ErrorCode     int LLBLError AllOk                                           Throw error                      throw new Exception  Stored Procedure  sp courier user SelectAll  reported the ErrorCode      i32ErrorCode                                      return toReturn                            catch  Exception ex                                   some error occured  Bubble it to caller and encapsulate Exception object                 throw new Exception  Courier user  SelectAll  Error occured    ex                             finally                               if  blnMainConnectionIsCreatedLocal                                           Close connection                      sqMainConnection Close                                      cmdToExecute Dispose                    adapter Dispose

User · Answer

Before you begin to curse your application you need to check this:

Is your application the only one using that instance of SQL Server. a. If the answer to that is NO then you need to investigate how the other applications are consuming resources on your SQl Server.run b. If the answer is yes then you must investigate your application.
Run SQL Server Profiler and check what activity is happening in other applications (1a) using SQL Server and check your application as well (1b).
If indeed your application is starved off of resources then you need to make farther investigations. For more read on this http://sqlserverplanet.com/troubleshooting/sql-server-slowness

User · Answer

May be this is alltime multiple connection open issue  you are somewhere in your code opening connections and not closing them properly  use    using  SqlConnection con   new SqlConnection connectionString                         con Open                 Refer this article  http   msdn microsoft com en-us library ms254507 v vs 80  aspx  The Using block in Visual Basic or C  automatically disposes of the connection when the code exits the block  even in the case of an unhandled exception

User · Answer

Here s what u can also try      run your application    while it is still running  launch your command prompt  while your application is running type netstat -n on the command prompt   You should see a list of TCP IP connections   Check if your list is not very long   Ideally you should have less than 5 connections in the list   Check the status of the connections  If you have too many connections with a TIME WAIT status it means the connection has been closed and is waiting for the OS to release the resources   If you are running on Windows  the default ephemeral port rang is between 1024 and 5000 and the default time it takes Windows to release the resource from TIME WAIT status is 4 minutes   So if your application used more then 3976 connections in less then 4 minutes  you will get the exception you got   Suggestions to fix it    Add a connection pool to your connection string    If you continue to receive the same error message  which is highly unlikely  you can then try the following   Please don t do it if you are not familiar with the Windows registry    Run regedit from your run command   In the registry editor look for this registry key  HKEY LOCAL MACHINE SYSTEM CurrentControlSet Services Tcpip Parameters    Modify the settings so they read   MaxUserPort         dword 00004e20  10 000 decimal  TcpTimedWaitDelay   dword 0000001e  30 decimal   This will increase the number of ports to 10 000 and reduce the time to release freed tcp ip connections   Only use suggestion 2 if 1 fails   Thank you

User · Answer

Check against any long running queries in your database   Increasing your pool size will only make your webapp live a little longer  and probably get a lot slower   You can use sql server profiler and filter on duration   reads to see which querys need optimization   I also see you re probably keeping a global connection   blnMainConnectionIsCreatedLocal   Let  net do the pooling for you and open   close your connection with a using statement   Suggestions    Always open and close a connection like this  so  net can manage your connections and you won t run out of connections       using  SqlConnection conn   new SqlConnection connectionString              conn Open            do some stuff         conn disposed  As I mentioned  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

[c#] How to Solve Max Connection Pool Error

Examples related to c#

Examples related to asp.net

Examples related to sql-server