title: EF6 executing a long running stored procedure gets timeout errors
body: <p>I recently upgraded my project from EF5 to EF6. In this project I have an Azure Worker Role that runs periodically, and kicks off a stored procedure on SQL Azure that updates a bunch of database information, and takes on average 1.5 hours to execute. When it is done, the worker role performs additional tasks with the return result of the stored procedure.</p>
  
  <p>This used to work flawlessly in EF5, but in EF6 it fails every time with one of these errors:</p>
  
  <blockquote>
    <p>Error A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)</p>
    
    <p>Error The session has been terminated because it has acquired too many locks. Try reading or modifying fewer rows in a single transaction. A severe error occurred on the current command. The results, if any, should be discarded.</p>
  </blockquote>
  
  <p>I have tried the following things to fix the error:</p>
  
  <ol>
  <li>Verified that all stored procedure reads have the <code>WITH (NOLOCK)</code> modifier</li>
  <li>Increased the timeout on the entity framework context to 5 hours</li>
  <li>Removed the new <code>SqlAzureExecutionStrategy</code> that was put in place and brought it back down to use <code>DefaultExecutionStrategy</code></li>
  <li>Removed any transactions that were occurring in the stored procedure</li>
  <li>Assure that this step in the worker role is running in it's own context</li>
  </ol>
  
  <p>Example of the code:</p>
  
  <pre><code>using (var dbContext = new EFEntityContext())
  {
      // set the timeout to 5 hours
      var objectContext = (dbContext as IObjectContextAdapter).ObjectContext;
      objectContext.CommandTimeout = 18000; // 5 hours
  
      // update all active curriculums
      var result = dbContext.usp_MyLongRunningProd();
  
      // log the results of the operation
      Trace.TraceInformation(result);
  }
  </code></pre>
  
  <p>Also, the stored procedures reads a large table into a cursor, loops through it, and perform as an analysis and data change based on each items. I don't need the cursor to be in a transaction of any kind, and am not using one that I know of, unless EF is making one and that is the issue.</p>