EF Code First - Timeout expired. The timeout period elapsed prior to completion

Question

Apology for this strangely worded question. I don't know what the actual problem is but hopefully someone can give me some insights.

I am getting the following error when trying to run migrations:

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

It is interesting to note that on my laptop this is not happening but on my VM (azure - large) this is happening with 100% failure rate.

I am using Ef 6.0.0 -rc1. Please note, updating EF is not an option. If updating to EF 6.0.0 or 6.0.1 I will get the following error with 100% failure rate:

Errors during Code First add-migration

I have also timed the error. It takes about 1.5 min to trigger the error. When running with -Verbose flag it was trying to create 200 tables with indexes. Copying the sql query and excuting it in SSMS takes 5 secs.

A few things that I have tired but didn't work:

1) Setting ObjectContext.CommandTimeout = 36000 // 10 hours! as indicated here:

https://stackoverflow.com/a/6234593/305469

2) Setting timeout in connection string in "web.config":

connectionString="Data Source=localhost;Initial Catalog=myDB;Integrated Security=SSPI;Connection Timeout=36000"

3) Setting "machine.config" transaction maxTimeout:

<system.transactions> <machineSettings maxTimeout="00:00:00" /> </system.transactions>

4) Setting "remote query timeout" on sql server

USE MyDB;
GO
EXEC sp_configure 'remote query timeout', 0 ;
GO
RECONFIGURE ;
GO

So what is happening? How come CommandTimeout is not being respected? Any suggestions?

Full trace as follows:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TInterceptionContext,TResult](Func`1 operation, TInterceptionContext interceptionContext, Action`1 executing, Action`1 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
   at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection)
   at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClass32.<ExecuteStatements>b__2e()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, Boolean downgrading, Boolean auto)
   at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
   at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration)
   at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClassc.<Update>b__b()
   at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
   at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.Run()
   at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
   at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
   at System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner)
   at System.Data.Entity.Migrations.Design.ToolingFacade.Update(String targetMigration, Boolean force)
   at System.Data.Entity.Migrations.UpdateDatabaseCommand.<>c__DisplayClass2.<.ctor>b__0()
   at System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command)
ClientConnectionId:8cbbc70c-8182-417e-9aca-4603f797340d
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Solution 1

In the constructor of Configuration.cs class (in migration Folder) add the property CommandTimeout = Int32.MaxValue;

Solution 2

I restarted the SQL Server service (Win7 - Computer Management > Services and Applications > Services)

Solution 3

An FYI EF Migrations pull their timeout from a separate configuration:

public class MyContextConfiguration : DbMigrationsConfiguration<MyContext>
{
    public MyContextConfiguration()
    {
        CommandTimeout = 900;
    }
}

Change the 900 to a something higher, all of the other SQL timeout changes (web.config, etc.) did not do anything, this worked for me.

Solution 4

For me, the problem was that that migration script took a long time to run (15 minutes).

This is how I worked around the issue:
1. Run update-database -script (-force may be necessary)
2. Copy this SQL script output and run in SQL Server Management Studio

View more solutions

Comments

[Den Pakizh]

Can you add stacktrace?

[Chi Chan]

Sure thing @DenPakizh, I have added the stacktrace to the question.

[Shane Neuville]

1) From stacktrace looks like migrations don't use ObjectContext 2) AFAIK this timeout is the timeout for establishing a connection not the timeout assoicated with executing the actual statement 3,4) My guess is that EF Code Migrations set a 90 second timeout on the SQLCommand and that's what's governing the amount of time for a timeout Can you run SQL Server profiler to see what the last query executed is? Or use technet.microsoft.com/en-us/library/ms175518.aspx to locate blocking ?

[voidmain]

Adding -force didn't work for me. The migration I had to apply was large and I was using EF6 which is inefficient in comparison to 6.1 as noted above. In the VS project Migrations folder there is the class Configuration.cs. In the constructor of that class I added the property CommandTimeout = Int32.MaxValue; That change permitted the update-database command to finally complete without an error.

[Charl]

@voidmain -force did not work for me either, your CommandTimeout setting worked perfectly. Thanks, worthy comment.

[FLCL]

Helped me when simple query started to raise this error for no reason, ty.

[Stephan]

@BastienVandamme It's in second. docs.microsoft.com/en-us/dotnet/api/…

[DanteTheSmith]

@FLCL If this helped the query did not raise it for no reason, the reason was the execution time was too long. Otherwise raising this would not help, the default time is more than enough for a "simple" query. Maybe that query is not so simple, fire up the profiler and look at execution time.

[FLCL]

@DanteTheSmith It can be because of locks that stay due to transaction not closed properly.

[IRONicMAN]

Dangerous as it will give let every query or command take more time before eventually throwing an exception.

[ransems]

Yes, I only change it for deployments, then change back to normal. but for big DBs and even small changes this works. As a dev trying to work within the framework, this process worked wonders.

[pat8719]

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review