Oracle – timeout period elapsed. all pooled connections were in use and max pool size reached

asp.netoraclepoolsize

After some time the following error page is shown.

What can I do to prevent this?

I have large numbers of users and the app uses Oracle 11g. Please suggest me pool size so can be extended default max pool size is 100.

I have checked all connections properly closed. I am using OracleDataReader and Datatable in my application Method that i am using is as under:

 public OracleDataReader BidNoIncr()
    {
        OracleConnection objOracleConnection = new OracleConnection(objDBClass.GetConnSring());
        OracleDataReader objDataReader;
        string strQuery = "Select max(BID_NO)+1 as SNumber from HH_BIDS";
        OracleCommand objOracleCommand = new OracleCommand(strQuery, objOracleConnection);
           objOracleConnection.Open();
           objDataReader = objOracleCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);       
            return objDataReader;

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. Description:
An unhandled exception occurred during the execution of the current
web request. Please review the stack trace for more information about
the error and where it originated in the code.

Exception Details: 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.

Source Error: An unhandled exception was generated during the
execution of the current web request. Information regarding the origin
and location of the exception can be identified using the exception
stack trace below.

Stack Trace:

[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.]
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection) +309609
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory) +125
System.Data.OracleClient.OracleConnection.Open() +43
DbClass.GetConnSring() +58 DBViewRec.ViewSalvageItems() +53
viewsalvageitems.ShowRecords() +44
viewsalvageitems.Page_Load(Object sender, EventArgs e) +5
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object
o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender,
EventArgs e) +35 System.Web.UI.Control.OnLoad(EventArgs e) +91
System.Web.UI.Control.LoadRecursive() +74
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
+2207

Best Answer

In general, this error may occur in the following situations:

1) You have a very large number of users using your database in the same time and you run out of free connections. Possible solutions: increase number of allowed connections on your server and/or (if your system is a webserver) increase the pool size specified in database connection string.

2) Your system has poor database logic design and/or connection leaks like when connection open isn't closed properly later. Solution for this will be auditing your code for such connection leaks and fixing them by properly closing connections all the time.

Related Solutions

.net – How to solve a connection pool problem between ASP.NET and SQL Server

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();                
}

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 "Why Does a Connection Pool Overflow?" 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.

C# – Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated

Looks like you have a query that is taking longer than it should. From your stack trace and your code you should be able to determine exactly what query that is.

This type of timeout can have three causes;

There's a deadlock somewhere
The database's statistics and/or query plan cache are incorrect
The query is too complex and needs to be tuned

A deadlock can be difficult to fix, but it's easy to determine whether that is the case. Connect to your database with Sql Server Management Studio. In the left pane right-click on the server node and select Activity Monitor. Take a look at the running processes. Normally most will be idle or running. When the problem occurs you can identify any blocked process by the process state. If you right-click on the process and select details it'll show you the last query executed by the process.

The second issue will cause the database to use a sub-optimal query plan. It can be resolved by clearing the statistics:

exec sp_updatestats

If that doesn't work you could also try

dbcc freeproccache

You should not do this when your server is under heavy load because it will temporarily incur a big performace hit as all stored procs and queries are recompiled when first executed. However, since you state the issue occurs sometimes, and the stack trace indicates your application is starting up, I think you're running a query that is only run on occasionally. You may be better off by forcing SQL Server not to reuse a previous query plan. See this answer for details on how to do that.

I've already touched on the third issue, but you can easily determine whether the query needs tuning by executing the query manually, for example using Sql Server Management Studio. If the query takes too long to complete, even after resetting the statistics you'll probably need to tune it. For help with that, you should post the exact query in a new question.

Best Answer

Related Solutions

.net – How to solve a connection pool problem between ASP.NET and SQL Server

C# – Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated

Related Topic