Timeout period elapsed prior to obtaining a connection from the pool

Question

I am getting following error after hosting the application:

Error Page : http://********.in/Presentation Layer/NewQuotation.aspx

Error Message : 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.

StackTrace : at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.Practices.EnterpriseLibrary.Data.Database.GetNewOpenConnection() at Microsoft.Practices.EnterpriseLibrary.Data.Database.GetOpenConnection(Boolean disposeInnerConnection) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) at DataLayer.Quotation_DL.insertQuoteproduct(QuotationProductCombination _objQP) in F:\gayathri reddy\Enq Quote\06-01-2015\DataLayer\Quotation_DL.cs:line 430 at BusinessLayer.Quotation_BL.insertQuoteproduct(QuotationProductCombination _objQpc) in F:\gayathri reddy\Enq Quote\06-01-2015\BusinessLayer\Quotation_BL.cs:line 88 at EngQuote.Presentation_Layer.NewQuotation.Quotationsave(Int32 status, String oper) in F:\gayathri reddy\Enq Quote\06-01-2015\EngQuote\Presentation Layer\NewQuotation.aspx.cs:line 3711

But while running the code in local Visual Studio 2010 there is no issues, when i host it in server or local machine IIS or Shared hosting server on that time only we are facing this issue

I have tried following all these steps but have not got any improvement

1).Opened and closed the connection in every DL function

2).Increased the command time out in this way DbCommand cmd = db.GetStoredProcCommand("Procedurename"); cmd.CommandTimeout = int.MaxValue; SqlDataReader sdata = (SqlDataReader)db.ExecuteReader(cmd);

3). Increased connection time out in web config

4). Increased executionTimeout in web config

5). Increased shutdown time out

6). Changed debug=false

but no use,connection time out problem still exists for long processes Please suggest any solution other than above options Expecting a good solution for this issue from u peoples thanks in advance....

Is the connection static? Do you use the using-statement to ensure that it gets closed as soon as possible? Show some code. — Tim Schmelter
DbConnection con = sqldb.CreateConnection(); try { con.Open(); int CusIdent = Convert.ToInt32(sqldb.ExecuteScalar("spInsertCustomerCompanyGroup", objCustGrp.GroupName, objCustGrp.CreatedBy, objCustGrp.CreatedDate)); return CusIdent; } finally { con.Close(); } — s.kantha samy
hi, i am not using (using statement) check following DbConnection con = sqldb.CreateConnection(); try { con.Open(); int CusIdent = Convert.ToInt32(sqldb.ExecuteScalar("spInsertCustomerCompanyGroup", objCustGrp.GroupName, objCustGrp.CreatedBy, objCustGrp.CreatedDate)); return CusIdent; } finally { con.Close(); } — s.kantha samy

devan devan · Accepted Answer · 2015-01-07T11:38:22

I guess connection between IIS and SQL Server was too slow to serve a connection. Just make sure the max connection limit is not reached. You can confirm by executing below select statement.

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

You can try increasing pool size in connection string. But this will have some downside. Below is the sample connection string with increased pool size. server=localhost;database=dbname;uid=username;pwd=paswrd;Max Pool Size=200;

Timeout period elapsed prior to obtaining a connection from the pool

1 Answers