none
Execution of the command requires an open and available connection. The connection's current state is broken. RRS feed

  • Question

  • We are operating an .NET 3.5 application on an IIS7 that connects to a SQL Server 2008 database through the ADO.Net Entity Framework. The whole database connection handling lies within the EF (we never manually open or close copnnection explicitly in our code). Connection settings are set to their defaults in the connection string we use in our app.config.

    The application has run fine for a while but now we encountered a servere problem:The data acess layer of our app (using EF) produces an exception any time the application needs to access the database. The error message reads "Execution of the command requires an open and available connection. The connection's current state is broken.". I pasted in the stack trace below. It look as if the connection have been shut down for some reason from the database side without the EF connection pool being aware of it and hence using stale connections from its pool. This is a very severe issue for us. Any hint on what to do in a situation like this will be highly appreciated

    System.InvalidOperationException: Execution of the command requires an open and available connection. The connection's current state is broken.

    at System.Data.Objects.ObjectContext.EnsureConnection()at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)at System.Data.Objects.ObjectQuery`1.Execute(MergeOption mergeOption)at MabaTrade.DataAccess.BaseDataProvider`1.Find(QueryParameters`1 parameters) in C:\TFS\MabaTrade\Prod\MabaTradeDataAccessLayer\DataAccess\BaseDataProvider.cs:line 391at MabaTrade.DataAccess.BaseDataProvider`1.GetByKey(EntityKey key, IList`1 includes, Boolean refreshFromStore) in C:\TFS\MabaTrade\Prod\MabaTradeDataAccessLayer\DataAccess\BaseDataProvider.cs:line 371at MabaTrade.DataAccess.BaseDataProvider`1.GetById(Int64 id, IList`1 includes, Boolean refreshFromStore) in C:\TFS\MabaTrade\Prod\MabaTradeDataAccessLayer\DataAccess\BaseDataProvider.cs:line 260at MabaTrade.DataAccess.BaseDataProvider`1.GetById(Int64 id, Boolean refresh) in C:\TFS\MabaTrade\Prod\MabaTradeDataAccessLayer\DataAccess\BaseDataProvider.cs:line 205at MabaTrade.DataAccess.BaseDataProvider`1.GetById(Int64 id) in C:\TFS\MabaTrade\Prod\MabaTradeDataAccessLayer\DataAccess\BaseDataProvider.cs:line 192at MabaTrade.BusinessDomain.Converter.EBVEngine.getProduct(Product product) in C:\TFS\MabaTrade\Prod\MabaTradeBusinessLayer\BusinessDomain\Converter\EBVEngine.cs:line 191at MabaTrade.BusinessDomain.Converter.EBVEngine.GetEBVRate(UOM uom, Product product, Currency currency, DateTime refDate) in C:\TFS\MabaTrade\Prod\MabaTradeBusinessLayer\BusinessDomain\Converter\EBVEngine.cs:line 92at MabaTrade.BusinessDomain.Converter.EBVEngine.GetEBVRate(Int64 uomId, Int64 productId, Int64 currencyId, DateTime refDate) in C:\TFS\MabaTrade\Prod\MabaTradeBusinessLayer\BusinessDomain\Converter\EBVEngine.cs:line 74at MabaTrade.BusinessDomain.CustomerDesktop.SpotRackRiskCalculator.calculateEBV(DateTime refDate, Int64 productId, UOM uom, Decimal baseQty) in C:\TFS\MabaTrade\Prod\MabaTradeBusinessLayer\BusinessDomain\CustomerDesktop\SpotRackRiskCalculator.cs:line 525at MabaTrade.BusinessDomain.CustomerDesktop.SpotRackRiskCalculator.calculateDealDetailEBV(vwFutureDeliveriesDealDetails detail, UOM qtyUOM) in C:\TFS\MabaTrade\Prod\MabaTradeBusinessLayer\BusinessDomain\CustomerDesktop\SpotRackRiskCalculator.cs:line 511at MabaTrade.BusinessDomain.CustomerDesktop.SpotRackRiskCalculator.calculateFutureDeliveries() in C:\TFS\MabaTrade\Prod\MabaTradeBusinessLayer\BusinessDomain\CustomerDesktop\SpotRackRiskCalculator.cs:line 290at MabaTrade.BusinessDomain.CustomerDesktop.SpotRackRiskCalculator.CalculateFigures(SpotRackRiskValue toBeCalculated) in C:\TFS\MabaTrade\Prod\MabaTradeBusinessLayer\BusinessDomain\CustomerDesktop\SpotRackRiskCalculator.cs:line 101at MabaTrade.BusinessDomain.CustomerDesktop.SpotRackRiskCalculator.CalculateFigures() in C:\TFS\MabaTrade\Prod\MabaTradeBusinessLayer\BusinessDomain\CustomerDesktop\SpotRackRiskCalculator.cs:line 141at MabaTrade.BusinessDomain.CustomerDesktop.CustomerDesktopManager.LoadSpotRackRisk(Int64 customerId, Int64 targetUOMId) in C:\TFS\MabaTrade\Prod\MabaTradeBusinessLayer\BusinessDomain\CustomerDesktop\CustomerDesktopManager.cs:line 638at MabaTrade.ServiceCommands.CustomerDesktopLoadSpotRackRiskCmd.executeCommand() in C:\TFS\MabaTrade\Prod\MabaTradeServiceLayer\Commands\CustomerDesktop\CustomerDesktopLoadSpotRackRiskCmd.cs:line 51at MabaTrade.ServiceCommands.ServiceCommand`1.Execute(CommandInitialization initializeCommand) in C:\TFS\MabaTrade\Prod\MabaTradeServiceLayer\Commands\ServiceCommand.cs:line 89

     

     

    Thursday, April 22, 2010 10:02 AM

Answers

  • Hello TSBoe,

     

    Thank you for reporting this issue!    Could you please provide us with more detailed information about your application?   Like some sample codes which thrown the exception, how often the exception will be thrown.   I will do my best to help!

     

    Besides, if it is not local connection, there could be other factors that impact the connection, like network.   Please try to catch the exception and use ObjectContext.Connection.Open() to reopen the connection.   If it fails, it is likely the network is down or the server has some problems.  

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, April 23, 2010 3:13 AM
    Moderator
  • Hi Lingzhi,

    thanks for your reply first of all.

    We were able to reproduce the error. It's somewhat related to the way some components within our applications handle their database access. For performance reasons (caching) we have some of our components implemented as singletons. Those components reside on the middle tier hosted by an IIS and are instatiated only one during the lifetime of the service (at first use) and resued afterwards as long as the IIS service stays active (which in our case it usually does for weeks unles we have to change anything). Durng this instantiation we assign an ObjectContext to the component that it is supposed to used for all database activity during its lifetime. Creating an EF ObjectContext and setting it of the singleto component includes grabbing a connection from the connection pool and associating it with the ObjectContext.

    Since the component was alive on the IIS for weeks it still had its initial connection associated. It must then have happened somehow that the connection it held broke or times out or the max connection lifetime on the database server expired. This led to the error the next time the component tries to access the database (and all subsequent times). Looks like the ObjectCpntext held a stale connection (even calling ObjectContext.IsOpen produces a "true" in this situation so that there is no way for the service to see that the connection is broken, all you cabn do is start a database operation and catch the exception) and wasn't able to recover from the exception (e.g. by asking the pool for a fresh connection). I guess this is due to the fact that once an ObjectContext gets a copnnection fro the pool it will never refersh it by asking the pool for a new one.

    This was something we hadn't planned for. We worked arount it by using a fresh ObjectContext (with a fresh connection) each time any of our singleton components needs to access the database now. This comes with a slight performance penalty due to the fact that we have to instantiate a new ObjectContext over and oer again but we didn't see any other was to handle this scenario.

    Regards,

    Thomas.

    Friday, April 30, 2010 11:03 AM
  • I've had the exact same problem, code ran fine for weeks and then all of a sudden got this error regularly.  I first implemented the solution you came up with and that resolved the problem, though at a cost.  We then changed it back to the original version, but used a lock around every place the ObjectContext is actually queried and enumerated (we got lucky that it was only in two spots and both places were querying and enumerating in the same method).  That fix has only been in for a couple days now, and time seems to be a factor in this issue, so I'm still waiting to see if it actually solved it in the long run.

    I bet if you review your SQL logs, you will see a 17886 error that corresponds with when you first got the connection broken error.

     

     

    Friday, April 30, 2010 3:37 PM

All replies

  • Hello TSBoe,

     

    Thank you for reporting this issue!    Could you please provide us with more detailed information about your application?   Like some sample codes which thrown the exception, how often the exception will be thrown.   I will do my best to help!

     

    Besides, if it is not local connection, there could be other factors that impact the connection, like network.   Please try to catch the exception and use ObjectContext.Connection.Open() to reopen the connection.   If it fails, it is likely the network is down or the server has some problems.  

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, April 23, 2010 3:13 AM
    Moderator
  • Hi TSBoe,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, April 27, 2010 1:47 AM
    Moderator
  • Hi Lingzhi,

    thanks for your reply first of all.

    We were able to reproduce the error. It's somewhat related to the way some components within our applications handle their database access. For performance reasons (caching) we have some of our components implemented as singletons. Those components reside on the middle tier hosted by an IIS and are instatiated only one during the lifetime of the service (at first use) and resued afterwards as long as the IIS service stays active (which in our case it usually does for weeks unles we have to change anything). Durng this instantiation we assign an ObjectContext to the component that it is supposed to used for all database activity during its lifetime. Creating an EF ObjectContext and setting it of the singleto component includes grabbing a connection from the connection pool and associating it with the ObjectContext.

    Since the component was alive on the IIS for weeks it still had its initial connection associated. It must then have happened somehow that the connection it held broke or times out or the max connection lifetime on the database server expired. This led to the error the next time the component tries to access the database (and all subsequent times). Looks like the ObjectCpntext held a stale connection (even calling ObjectContext.IsOpen produces a "true" in this situation so that there is no way for the service to see that the connection is broken, all you cabn do is start a database operation and catch the exception) and wasn't able to recover from the exception (e.g. by asking the pool for a fresh connection). I guess this is due to the fact that once an ObjectContext gets a copnnection fro the pool it will never refersh it by asking the pool for a new one.

    This was something we hadn't planned for. We worked arount it by using a fresh ObjectContext (with a fresh connection) each time any of our singleton components needs to access the database now. This comes with a slight performance penalty due to the fact that we have to instantiate a new ObjectContext over and oer again but we didn't see any other was to handle this scenario.

    Regards,

    Thomas.

    Friday, April 30, 2010 11:03 AM
  • I've had the exact same problem, code ran fine for weeks and then all of a sudden got this error regularly.  I first implemented the solution you came up with and that resolved the problem, though at a cost.  We then changed it back to the original version, but used a lock around every place the ObjectContext is actually queried and enumerated (we got lucky that it was only in two spots and both places were querying and enumerating in the same method).  That fix has only been in for a couple days now, and time seems to be a factor in this issue, so I'm still waiting to see if it actually solved it in the long run.

    I bet if you review your SQL logs, you will see a 17886 error that corresponds with when you first got the connection broken error.

     

     

    Friday, April 30, 2010 3:37 PM