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.

Sure thing @DenPakizh, I have added the stacktrace to the question. — Chi Chan, Nov 12 '13 at 06:00
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 http://technet.microsoft.com/en-us/library/ms175518.aspx to locate blocking ? — Shane Neuville, Mar 18 '14 at 01:11

score 25 · Answer 1 · answered Nov 26 '15 at 07:31

25

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

answered Nov 26 '15 at 07:31

Stephan

594
7
17

@BastienVandamme It's in second. https://learn.microsoft.com/en-us/dotnet/api/system.data.objects.objectcontext.commandtimeout?view=netframework-4.7.2 – Stephan Nov 19 '18 at 10:04

score 7 · Answer 2 · answered Jul 27 '16 at 09:55

7

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

answered Jul 27 '16 at 09:55

danmbuen

594
5
7

1

Helped me when simple query started to raise this error for no reason, ty. – FLCL Mar 06 '17 at 07:04
@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. – DanteTheSmith Jan 02 '19 at 10:10
1

@DanteTheSmith It can be because of locks that stay due to transaction not closed properly. – FLCL Jan 02 '19 at 20:04

score 4 · Answer 3 · answered Jun 09 '19 at 09:30

4

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 Jun 09 '19 at 09:30

ransems

641
7
19

Dangerous as it will give let every query or command take more time before eventually throwing an exception. – IRONicMAN Jul 12 '19 at 15:38
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. – ransems Jul 13 '19 at 13:15

score 2 · Answer 4 · answered May 24 '18 at 16:04

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

score 0 · Answer 5 · edited May 23 '17 at 12:02

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.

score 0 · Answer 6 · answered Sep 28 '15 at 18:13

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 Sep 28 '15 at 18:13

Craig Strohl

41
1

5

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. – voidmain Oct 06 '15 at 18:49
@voidmain -force did not work for me either, your CommandTimeout setting worked perfectly. Thanks, worthy comment. – Charl Dec 12 '15 at 19:34

score 0 · Answer 7 · answered Mar 12 '21 at 20:59

I ran into this error as well and tried using various command timeout settings and found nothing was working. After awhile I discovered that my issue wasn't a timeout issue but a SQL transaction issue. Apparently, my code was conflicting with a SQL transaction that I had previously ran. At the time, the transaction was not committed or rolled back. Once I ran my COMMIT statement the issue resolved itself.

Remember to always COMMIT or ROLLBACK your transactions.

Adriano Souza · Answer 8 · 2022-04-06T12:28:03.167

0

Verify if no has some transaction openned, this happened today with me and caused timeout to execute Scaffold-DbContext command, Because the DataBase is locked and the Scaffold-DbContext command can´t run.

Close all transaction openned and try again.

edited Apr 06 '22 at 12:28

answered Mar 31 '22 at 22:35

Adriano Souza

1
1

This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/31428654) – pat8719 Apr 02 '22 at 19:32

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

8 Answers8

Linked