How can I solve a connection pool problem between ASP NET and SQL Server

Question

The last few days we see this error message in our website too much    quot 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  quot   We have not changed anything in our code in a while  I revised the code to check open connections which didn t close  but found everything to be fine   How can I solve this   Do I need to edit this pool   How can I edit this pool s max number of connections   What is the recommended value for a high traffic website     Update  Do I need to edit something in IIS  Update  I found that the number of active connections are anywhere from 15 to 31  and I found that the max allowed number of connections configured in SQL server is more than 3200 connections  is 31 too many or should I edit something in the ASP NET configration

User · Answer

Upon installing  NET Framework v4 6 1 our connections to a remote database immediately started timing out due to this change    To fix simply add the parameter TransparentNetworkIPResolution in the connection string and set it to false      Server myServerName Database myDataBase Trusted Connection True TransparentNetworkIPResolution False

User · Answer

You can try that too  for solve timeout problem   If you didn t add httpRuntime to your webconfig  add that in  lt system web gt  tag   lt sytem web gt        lt httpRuntime maxRequestLength  20000  executionTimeout  999999   gt   lt  system web gt    and  Modify your connection string like this     lt add name  connstring  connectionString  Data Source DSourceName Initial Catalog DBName Integrated Security True Max Pool Size 50000 Pooling True   providerName  System Data SqlClient    gt    At last use       try                catch                finaly            connection close

User · Answer

I have encountered this problem too  when using some 3rd party data layer in one of my  NET applications  The problem was that the layer did not close the connections properly   We threw out the layer and created one ourselves  which always closes and disposes the connections  Since then we don t get the error anymore

User · Answer

Don t instantiate the sql connection too much times  Open one or two connections and use them for all next sql operations    Seems that even when Disposeing the connections the exception is thrown

User · Answer

I just had the same problem and wanted to share what helped me find the source  Add the Application name to your connection string and then monitor the open connections to the SQL Server select st text      es         ec   from sys dm exec sessions as es     inner join sys dm exec connections as ec on es session id   ec session id     cross apply sys dm exec sql text ec most recent sql handle  st where es program name     lt your app name here gt

User · Answer

You can specify minimum and maximum pool size by specifying MinPoolSize xyz and or MaxPoolSize xyz in the connection string  This cause of the problem could be a different thing however

User · Answer

We encounter this problem from time to time on our web site as well   The culprit in our case  is our stats indexes getting out of date   This causes a previously fast running query to  eventually  become slow and time out   Try updating statistics and or rebuilding the indexes on the tables affected by the query and see if that helps

User · Answer

Make sure you set up the correct settings for connection pool  This is very important as I have explained in the following article  https   medium com  dewanwaqas configurations-that-significantly-improves-your-app-performance-built-using-sql-server-and-net-ed044e53b60 You will see a drastic improvement in your application s performance if you follow it

User · Answer

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   var connection   new SqlConnection connectionString    connection Open       some code connection Close                     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      When your function returns a connection from a class method make sure you cache it locally and call its Close method  You ll leak a connection using this code for example  var command   new OleDbCommand someUpdateQuery  getConnection      result   command ExecuteNonQuery    connection   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  If you use SqlDataReader or a OleDbDataReader  close them  Even though closing the connection itself seems to do the trick  put in the extra effort to close your data reader objects explicitly when you use them   This article  quot Why Does a Connection Pool Overflow  quot  from MSDN SQL Magazine explains a lot of details and suggests some debugging strategies   Run sp who or sp who2  These system stored procedures return information from the sysprocesses system table that shows the status of and information about all working processes  Generally  you ll see one server process ID  SPID  per connection  If you named your connection by using the Application Name argument in the connection string  your working connections will be easy to find  Use SQL Server Profiler with the SQLProfiler TSQL Replay template to trace open connections  If you re familiar with Profiler  this method is easier than polling by using sp who  Use the Performance Monitor to monitor the pools and connections  I discuss this method in a moment  Monitor performance counters in code  You can monitor the health of your connection pool and the number of established connections by using routines to extract the counters or by using the new  NET PerformanceCounter controls

User · Answer

Unless your usage went up a lot  it seems unlikely that there is just a backlog of work  IMO  the most likely option is that something is using connections and not releasing them promptly  Are you sure you are using using in all cases  Or  through whatever mechanism  releasing the connections

User · Answer

I was facing the same problem  after hours of research I realized I was connected on Guest network without VPN so setting up VPN did the trick for me

User · Answer

Use this   finally       connection Close        connection Dispose        SqlConnection ClearPool

User · Answer

In my case  I had infinite loop  from a get Property trying to get value from database  which kept opening hundreds of Sql connections   To reproduce the problem try this   while  true        using  SqlConnection connection   new SqlConnection connectionString                 connection Open            someCall connection

User · Answer

Yet another reason happened in my case  because of using async await  resulting in the same error message   System InvalidOperationException   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    Just a quick overview of what happened  and how I resolved it   hopefully this will help others in the future  Finding the cause This all happened in an ASP NET Core 3 1 web project with Dapper and SQL Server  but I do think it is independent of that very kind of project  First  I have a central function to get me SQL connections  internal async Task lt DbConnection gt  GetConnection         var r   new SqlConnection GetConnectionString         await r OpenAsync   ConfigureAwait false       return r     I m using this function in dozens of methods like e g  this one  public async Task lt List lt EmployeeDbModel gt  gt  GetAll         await using var conn   await GetConnection        var sql     quot SELECT   FROM Employee quot        var result   await conn QueryAsync lt EmployeeDbModel gt  sql       return result ToList       As you can see  I m using the new using statement without the curly braces         so disposal of the connection is done at the end of the function  Still  I got the error about no more connections in the pool being available  I started debugging my application and let it halt upon the exception happening  When it halted  I first did a look at the Call Stack window  but this only showed some location inside System Data SqlClient  and was no real help to me   Next  I took a look at the Tasks window  which was of a much better help   There were literally thousands of calls to my own GetConnection method in an  quot Awaiting quot  or  quot Scheduled quot  state  When double-clicking such a line in the Tasks window  it showed me the related location in my code via the Call Stack window  This helped my to find out the real reason of this behaviour  It was in the below code  just for completeness    Route nameof LoadEmployees    public async Task lt IActionResult gt  LoadEmployees      DataSourceLoadOptions loadOption        var data   await CentralDbRepository EmployeeRepository GetAll         var list           data Select async d   gt                                var values   await CentralDbRepository EmployeeRepository GetAllValuesForEmployee d                   return await d ConvertToListItemViewModel                      values                      Config                      CentralDbRepository                               ToListAsync        return Json DataSourceLoader Load await list  loadOption       In the above controller action  I first did a call to EmployeeRepository GetAll   to get a list of models from the database table  quot Employee quot   Then  for each of the returned models  i e  for each row of the result set   I did again do a database call to EmployeeRepository GetAllValuesForEmployee d   While this is very bad in terms of performance anyway  in an async context it behaves in a way  that it is eating up connection pool connections without releasing them appropriately  Solution I resolved it by removing the SQL query in the inner loop of the outer SQL query  This should be done by either completely omitting it  or if required  move it to one multilpe JOINs in the outer SQL query to get all data from the database in one single SQL query  tl dr   lessons learned Don t do lots of SQL queries in a short amount of time  especially when using async await

User · Answer

This is mainly due to the connection not been closed in the application  Use  MinPoolSize  and  MaxPoolSize  in the connection string

User · Answer

In my case  I was not closing the DataReader object  using  SqlCommand dbCmd   new SqlCommand  quot  StoredProcedureName  quot    using  dbCmd Connection   new SqlConnection WebConfigurationAccess ConnectionString         dbCmd CommandType   CommandType StoredProcedure         Add parametres     dbCmd Parameters Add new SqlParameter  quot  ID quot   SqlDbType Int   Value   ID                            dbCmd Connection Open        var dr   dbCmd ExecuteReader      created a Data reader here     dr Close         gotta close the data reader       dbCmd Connection Close      don t need this as  using  statement should take care of this in its implicit dispose method

User · Answer

I wasn t thinking this was my issue at first but in running through this list I discovered that it didn t cover what my issues was  My issue was that I had a bug in which it tried to write the same record numerous times using entity framework   It shouldn t have been doing this  it was my bug   Take a look at the data you are writing   My thoughts are that SQL was busy writing a record  possibly locking and creating the timeout   After I fixed the area of code that was attempting to write the record multiple in sequential attempts  the error went away

User · Answer

This problem I have encountered before  It ended up being an issue with the firewall  I just added a rule to the firewall  I had to open port 1433 so the SQL server can connect to the server

User · Answer

You have leaked connections on your code  You may try to use using to certify that you re closing them  using  SqlConnection sqlconnection1   new SqlConnection    Server    SQLEXPRESS  Integrated security sspi connection timeout 5             sqlconnection1 Open        SqlCommand sqlcommand1   sqlconnection1 CreateCommand        sqlcommand1 CommandText      raiserror     This is a fake exception     17 1          sqlcommand1 ExecuteNonQuery       this throws a SqlException every time it is called      sqlconnection1 Close      Still never gets called       Here sqlconnection1 Dispose is  guaranteed   https   blogs msdn microsoft com angelsb 2004 08 25 connection-pooling-and-the-timeout-expired-exception-faq

User · Answer

Yes  There is a way to change configuration  If you are on a dedicated server and simply need more SQL connections  you may update the  max pool size  entries in both connection strings by following these instructions    Log into your server using Remote Desktop Open My Computer  Windows - E  and go to C  inetpub vhosts domain  httpdocs Double click on the web config file   This may just be listed as web if the file structure is set to hide extensions  This will open up Visual Basic or similar editor  Find your Connection Strings  these will look similar to the examples below      add name  SiteSqlServer  connectionString  server  local  database dbname uid dbuser pwd dbpassword pooling true connection lifetime 120 max pool size 25      5 Change the max pool size X value to the required pool size    Save and close your web config file

User · Answer

This problem i had in my code  I will paste some example code i have over came below error  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    String query    insert into STATION2 ID CITY STATE LAT N LONG W  values      a1           b1           c1           d1           f1                     d1           f1           g1           SqlConnection con   new SqlConnection mycon       con Open        SqlCommand cmd   new SqlCommand        cmd CommandText   query      cmd Connection   con      cmd ExecuteNonQuery          con Close        You want to close the connection each and every time  Before that i didn t us the close connect due to this i got error  After adding close statement i have over came this error

User · Answer

In addition to the posted solutions  In dealing with 1000 pages of legacy code  each calling a common GetRS multiple times  here is another way to fix the issue  In an existing common DLL  we added the CommandBehavior CloseConnection option  static public IDataReader GetRS String Sql        SqlConnection dbconn   new SqlConnection DB GetDBConn         dbconn Open        SqlCommand cmd   new SqlCommand Sql  dbconn       return cmd ExecuteReader CommandBehavior CloseConnection         Then in each page  as long as you close the data reader  the connection is also automatically closed so connection leaks are prevented  IDataReader rs   CommonDLL GetRS  quot select   from table quot    while  rs Read             do something   rs Close         this also closes the connection

User · Answer

If you are working on complex legacy code where a simple using          isn t possible - as I was - you may want to check out the code snippet I posted in this SO question for a way to determine the call stack of the connection creation when a connection is potentially leaked  not closed after a set timeout   This makes it fairly easy to spot the cause of the leaks

User · Answer

Did you check for DataReaders that are not closed and response redirects before closing the connection or a datareader  Connections stay open when you dont close them before a redirect

User · Answer

I also got this exact error log on my AWS EC2 instance  There were no connection leaks since I was just deploying the alpha application  no real users   and I confirmed with Activity Monitor and sp who that there are in fact no connections to the database  My issue was AWS related - more specifically  with the Security Groups  See  only certain security groups had access to the RDS server where I hosted the database  I added an ingress rule with authorize-security-group-ingress command to allow access to the correct EC2 instance to the RDS server by using --source-group-name parameter  The ingress rule was added  I could see that on the AWS UI - but I got this error  When I removed and then added the ingress rule manually on AWS UI - suddenly the exception was no more and the app was working

[.net] How can I solve a connection pool problem between ASP.NET and SQL Server?

Examples related to .net

Examples related to asp.net

Examples related to sql-server

Examples related to sql-server-2005