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

22

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.
c#
sql-server
entity-framework
ef-migrations
asked on Stack Overflow Nov 11, 2013 by Chi Chan • edited May 23, 2017 by Community

6 Answers

17

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

answered on Stack Overflow Nov 26, 2015 by Stephan
6

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

answered on Stack Overflow Jul 27, 2016 by danmbuen
1

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

answered on Stack Overflow May 24, 2018 by Josh
0

I ran into this in my production environment because it was producing queries like the ones here: Why does Entity Framework 6 generate complex SQL queries for simple lookups?

This is actually related to a bug in this version of EF: https://entityframework.codeplex.com/workitem/2083

They changed the default null semantics from 5 to 6 so I'm guessing you had the same problem I did after upgrading. My machine had a fraction of the data as my remote installation and until I got to production I didn't know I had a performance issue. The queries will often produce a table scan which will time out for larger tables.

To change it back so that it works like EF5 you have to set:

DbContextConfiguration.UseDatabaseNullSemantics = true

See here: http://msdn.microsoft.com/en-us/library/system.data.entity.infrastructure.dbcontextconfiguration.usedatabasenullsemantics(v=vs.113).aspx

The bug was fixed in EF 6.1 but you still have to set the option above to get simple where conditions.

answered on Stack Overflow Jul 30, 2014 by GrokSrc • edited May 23, 2017 by Community
0

I just had the same exact issue, i know this thread is a year old but maybe it will help someone else.

I was trying to create the database in entity 5 using the Package Manager Console using the connection string below.

update-database -ConfigurationTypeName My.Project.EF.Migrations.Configuration -ConnectionStringName MyDatabaseDev -ProjectName Project.Name -StartUpProjectName Database.Name

Each time i ran it i got the error below.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

To fix it i simply added the -force parameter and it went right through.

answered on Stack Overflow Sep 28, 2015 by Craig Strohl
0

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.

answered on Stack Overflow Jun 9, 2019 by ransems

User contributions licensed under CC BY-SA 3.0