c# - SQLConnection Leakage Issue (Timeout Expired due to Pooling - ASP.NET / SQL Server) -

i developing web application based on asp.net 4.0, jquery, ajax, javascript using sql server 2008 database. our application erp application has around 400 500 users using application. have made live since month ago.

now, i'm facing timeout issue , application crashed literally occurred after some hours of application usage.

server error in '/' application.  --------------------------------------------------------------------------------  timeout expired.  timeout period elapsed prior obtaining connection     pool.  may have occurred because pooled connections in use , max     pool size reached.  description: unhandled exception occurred during execution of current web     request. please review stack trace more information error ,     originated in code.   exception details: system.invalidoperationexception: timeout expired.  timeout     period elapsed prior obtaining connection pool.  may have occurred     because pooled connections in use , max pool size reached.  source error:   line 36:         param[1].value = password; line 37:  line 38:         return sqlhelper.executedataset(cdsglobal.connectionstring,     commandtype.storedprocedure, "validatelogin_sp", param); line 39:     } line 40:    source file: e:\abcd\app_code\user.cs    line: 38   stack trace:   [invalidoperationexception: timeout expired.  timeout period elapsed prior     obtaining connection pool.  may have occurred because pooled     connections in use , max pool size reached.]    system.data.providerbase.dbconnectionfactory.getconnection(dbconnection     owningconnection) +6352273    system.data.providerbase.dbconnectionclosed.openconnection(dbconnection     outerconnection, dbconnectionfactory connectionfactory) +6352606    system.data.sqlclient.sqlconnection.open() +300    dbconn.sqlhelper.executedataset(string connectionstring, commandtype commandtype,     string commandtext, sqlparameter[] commandparameters) +270    user.validatelogin(string employee_code, string password) in e:\abcd\app_code    \user.cs:38    user_userlogin.btnlogin_click(object sender, eventargs e) in e:\abcd    \user\userlogin.ascx.cs:79    system.web.ui.webcontrols.button.raisepostbackevent(string eventargument) +154    system.web.ui.page.processrequestmain(boolean includestagesbeforeasyncpoint,     boolean includestagesafterasyncpoint) +3707     -------------------------------------------------------------------------------- version information: microsoft .net framework version:4.0.30319; asp.net     version:4.0.30319.272  

for temporary resolution, restarting server , restarting mssqlserver service.

after searching above, came know due connection leakage. opened connections never closed.

i using ready made microsoft data access application block .net i.e. sqlhelper class in application data access using dataset , all.

note: not using sqldatareader anywhere.

i have examined class , closing connections , have never used manual connection code instead used sqlhelper everywhere.

i have following nested transactions structure in stored procedures follows:

begin try begin transaction  -- every below stored procedure has transation structure sp i.e. parent sp. (begin try, begin transaction etc)  exec sp1  exec sp2  exec sp3  --- ---   commit transaction end try  begin catch  if @@trancount>0   rollback transaction   print 'error occured in sp' end catch 

now, problem why connection leakage occurring.

1. is above transaction structure responsible i.e blocking transactions , requests.

2. if request in process , user has closed browser, application closes connection?

any appreciated.

solved !!

it not due nested transactions due bad indexes strategy used. used normal indexes , indexes include solve performance issues.

also ran sql profiler examine most read , writes on columns , accordingly applied indexes , review maintenance , execution plans. !!