We have a large web application in asp.net mvc using EF6 and SQL Server 2012.

We have two environments: staging and production.

Every time we have a release, we first deploy the same code to set up and restore the production database on the server for testing purposes. It has always worked for us.

Now when we restore the production db to the staging server we get some timeouts for some commands. What's strange is that after a few hours we stop getting timeouts.

This is the stacktrace of one of the timeouts:

[Win32Exception (0x80004005): The wait operation timed out]

[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +388
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +688
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4403
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +82
   System.Data.SqlClient.SqlDataReader.get_MetaData() +135
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6664141
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +6666008
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +577
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +107
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +288
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +180
   Glimpse.Ado.AlternateType.GlimpseDbCommand.ExecuteDbDataReader(CommandBehavior behavior) +847
   System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(Func`1 operation, TInterceptionContext interceptionContext, Action`1 executing, Action`1 executed) +72
   System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) +306
   System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues) +417
   System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() +218

[UpdateException: An error occurred while updating the entries. See the inner exception for details.]
   System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() +537
   System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction(Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) +627
   System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy) +212
   System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func`1 operation) +263
   System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options) +262
   System.Data.Entity.Internal.InternalContext.SaveChanges() +218

[DbUpdateException: An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.]
   System.Data.Entity.Internal.InternalContext.SaveChanges() +291
   *******.Services.DbTransaction.TransactionManager.SaveChanges() in c:\BuildAgent\work\c357fed3de014622\Source\Services\*******.Services\*******.Services\DbTransaction\TransactionManager.cs:28
   System.Web.Mvc.Async.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41() +112
   System.Web.Mvc.Async.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41() +452
   System.Web.Mvc.Async.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41() +452
   System.Web.Mvc.Async.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41() +452
   System.Web.Mvc.Async.<>c__DisplayClass33.<BeginInvokeActionMethodWithFilters>b__32(IAsyncResult asyncResult) +15
   System.Web.Mvc.Async.<>c__DisplayClass2b.<BeginInvokeAction>b__1c() +37
   System.Web.Mvc.Async.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult) +241
   System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) +29
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +111
   System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +53
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +19
   System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__5(IAsyncResult asyncResult, ProcessRequestState innerState) +51
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +111
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +606
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +288

      

        
        
        
      

    

We have an action filter that calls the method context.SaveChanges()

if everything goes well on request (so we don't have to do it manually in every action). This is why there is no action in the stacktrace, it will expire when called SaveChanges()

when the action is complete.

There are a lot of things in this request, so I am not showing the code, we do the usual things: update some rows, insert new ones, etc. It has some complex commands, but not complex enough for db to timeout. And when we run the code on our dev machines with the same db product, it works fine.

The weird thing is that it stops syncing after a few hours. But we cannot risk production.

Now I know that it is unlikely that you can give me a solution with just the information provided. But I would like to know some tips, pointers, tips, where to look, what to check, etc.

Edit

Our server has 12 GB of RAM and an Intel Xeon E5-2680 @ 2.80 GHz.

Our site works only there.

Our database is less than 4 GB

And we don't get timeout on our dev machines.