My ASP.NET 4.0 production application has recurring issues that make the site unresponsive due to timeout errors.
Here is a brief overview of the application. The application is located on three servers; a web server, an application server, and a database server running SQL Server 2008. All servers are running Windows Server 2008. The web server is in a public domain. The application server is located in the DMZ with open communication between the web server through ports 80 and 443 through WCF. The database server is located in a private domain with open communication with the application server through port 1433. The application has been running for some time and often experiences these problems last week. There were no code changes, and the hosting provider said that there were no changes on the server recently.
On the web server, the application experiences the error indicated below (Error 1). The quick fix solution is to restart the IIS process on the application server, but now this is a recurring problem and leads to serious interruptions for business owners.
DAL Applications uses Enterprise Library v4.1 to open database connections. I included 2 pieces of code that is responsible for initiating calls (code 1 and code 2). This method is repeated in other ways. Is it possible that the ExecuteReader method closes the connection incorrectly? There is no overriding method that allows me to specify ConnectionBehavior to close the connection.
We performed an analysis of the SQL profile and determined that there are no protracted open database connections.
While investigating the problem, someone suggested that the Enterprise Library might have an error in which the connections would not be deleted properly, but this was not confirmed in the message. How can I determine the cause of the problem? Or what would be the appropriate course of action to fix the problem? I could increase the size of the pool, but it looks like it will be just a temporary solution.
Error 1:
***System.ServiceModel.FaultException: 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. at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) 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 Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(String storedProcedureName, Object[] parameterValues) at CityStoreDAL.NavigationProvider.GetNavigator(Int32 navigatorID) in c:\TFS\CityStore\DEV\SRC\CityStoreDAL\NavigationProvider.cs:line 41 at CityStoreBLL.Navigation.GetNavigator(Int32 navigatorID) in c:\TFS\CityStore\DEV\SRC\CityStoreBLL\Navigation.cs:line 15 at CityStoreService.CityStoreService.GetNavigator(Int32 navigato rID) in c:\TFS\CityStore\DEV\SRC\CityStoreService\Navigation.cs:line 21 at SyncInvokeGetNavigator(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)*** Generated: Wed, 12 Nov 2014 19:40:22 GMT System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.ServiceModel.FaultException: 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. at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) 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 Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(String storedProcedureName, Object[] parameterValues) at CityStoreDAL.NavigationProvider.GetNavigator(Int32 navigatorID) in c:\TFS\CityStore\DEV\SRC\CityStoreDAL\NavigationProvider.cs:line 41 at CityStoreBLL.Navigation.GetNavigator(Int32 navigatorID) in c:\TFS\CityStore\DEV\SRC\CityStoreBLL\Navigation.cs:line 15 at CityStoreService.CityStoreService.GetNavigator(Int32 navigatorID) in c:\TFS\CityStore\DEV\SRC\CityStoreService\Navigation.cs:line 21 at SyncInvokeGetNavigator(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet) Server stack trace: at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc) at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout) at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation) at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message) Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at CityStoreWeb.CityStoreServiceReference.INavigation.GetNavigator(Int32 navigatorID) at CityStoreWeb.Product.<>c__DisplayClass1.<BindCategoryMenuAndInfo>b__0(INavigation proxy) in c:\TFS\CityStore\DEV\SRC\CityStoreWeb\Product.aspx.cs:line 73 at CityStoreWeb.Common.Service'1.Use(UseServiceDelegate'1 codeBlock) in c:\TFS\CityStore\DEV\SRC\CityStoreWeb\Common\Utils.cs:line 243 at CityStoreWeb.Product.BindCategoryMenuAndInfo(Int32 navigatorID) in c:\TFS\CityStore\DEV\SRC\CityStoreWeb\Product.aspx.cs:line 71 at CityStoreWeb.Product.SetupPage(Int32 navigatorID, Int32 categoryID, Int32 productID) in c:\TFS\CityStore\DEV\SRC\CityStoreWeb\Product.aspx.cs:line 64 at CityStoreWeb.Product.Page_Load(Object sender, EventArgs e) in c:\TFS\CityStore\DEV\SRC\CityStoreWeb\Product.aspx.cs:line 37 at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at CityStoreWeb.Common.BasePage.OnLoad(EventArgs e) in c:\TFS\CityStore\DEV\SRC\CityStoreWeb\Common\BasePage.cs:line 26 at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) --- End of inner exception stack trace --- at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.product_aspx.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
Code 1:
public static NavigatorEntity GetNavigator(int navigatorID) { Database db = DatabaseFactory.CreateDatabase(); object[] spParams; spParams = new object[1]; spParams[0] = navigatorID; using (IDataReader reader = db.ExecuteReader("GetNavigator", spParams)) { if (reader == null)k { throw new ArgumentNullException("reader"); } NavigatorEntity myNav = null; if (reader.Read()) { myNav = GetNavigatorEntityFromReader(reader); } if (myNav != null) { reader.NextResult(); myNav.Categories = GetCategoriesCollectionFromReader(reader); reader.NextResult(); myNav.RecommendedProducts = ProductProvider.GetRecommendedProductCollectionFromReader(reader, false); } return myNav; } }
Code 2:
public static List<NavigatorEntity> GetNavigatorsAll() { Database db = DatabaseFactory.CreateDatabase(); using (IDataReader myReader = db.ExecuteReader("GetNavigatorsAll")) { return GetNavigatorCollectionFromReader(myReader); } } private static List<NavigatorEntity> GetNavigatorCollectionFromReader(IDataReader reader) { List<NavigatorEntity> navigators = new List<NavigatorEntity>(); while (reader.Read()) { navigators.Add(GetNavigatorEntityFromReader(reader)); } return navigators; }
UPDATE:
Apparently, the problem is not that none of the methods referenced by ASP.NET errors. They threw an exception, because by the time they were reached, a problem (exhausted pool of connections) had already occurred, and an exception would be thrown for any processing method that opened a new connection.
After analyzing all the code at the Data Access Layer, I identified several rouge methods that did not use the corresponding using statement, thereby leaving open connections in the pool to accumulate. After highlighting the methods and using performance monitors to monitor the NumberOfPooledConnections, I confirmed that these methods are to blame.
The fix was to wrap blush methods in appropriate usage statements.
One of the methods causing the problem:
public static List<TaxCodeEntity> CMSGetTaxCodes() { Database db = DatabaseFactory.CreateDatabase(); return GetTaxCodeCollectionFromReader(db.ExecuteReader("CMS_GetTaxCodes")); }
Correction:
public static List<TaxCodeEntity> CMSGetTaxCodes() { Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCmd = db.GetStoredProcCommand("CMS_GetTaxCodes")) { using (IDataReader myReader = db.ExecuteReader(dbCmd)) { return GetTaxCodeCollectionFromReader(myReader); } } }