Azure SQL Database Operation Timeout

Question

I have a shared hosting site "production" that works perfectly. It has users and thus far I have no issues.

I decided to created a test environment. I created another Azure app and copied the code from my "production" site to this test site. This happen today. It has no users. I created a DB and I agreed to pay five bucks a month. So this is not a free account. I uploaded my seed data which part of it was 40K rows of zip code. That went fine without issues.

However, when I use the site, I consistently get timeout error. I can assure you that this is NOT because of usage. I increased the timeout from 30 to 90 in the connection string but that didn't make a difference. There is no code difference between the production and test.

I have read many post about this but none helped. Here are some samples:

post1 post2 post3 post4 post5 post6

Any ideas?

> [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.  This failure occurred while attempting to connect to the
> routing destination. The duration spent while attempting to connect to
> the original server was - [Pre-Login] initialization=27; handshake=25;
> [Login] initialization=0; authentication=0; [Post-Login] complete=3; 
> ]    System.Data.SqlClient.SqlConnection.OnError(SqlException
> exception, Boolean breakConnection, Action1 wrapCloseInAction)
> +2418094    System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection, Action1 wrapCloseInAction)
> +5694436    System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +285   
> System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream,
> BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
> stateObj, Boolean& dataReady) +3731   
> System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +58   
> System.Data.SqlClient.SqlDataReader.get_MetaData() +89   
> System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString) +379   
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
> async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader
> ds, Boolean describeParameterEncryptionRequest) +2064   
> System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
> method, TaskCompletionSource`1 completion, Int32 timeout, Task& task,
> Boolean asyncWrite) +375   
> System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
> method) +53   
> System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior, String method) +240   
> System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
> behavior) +41   
> System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
> +12    System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand
> entityCommand, CommandBehavior behavior) +435

Answer 1

It was the DTU issue. I had 5 DTU (S0) now I have 20 DTU (S1). If I have my physics right it's like a steam engine and BTU. Horsepower matters.

Answer 2

I was getting the same error but after looking a little deeper the issue I found was different and would like to share it if it helps someone else. I had an EntityFramework migration that ran fine on local development server and staging server but was taking too long on production. It was causing the timeout and since it was not a migration failed exception we did not think that would be the problem. In this migration we were indexing a table that had millions of records hence indexing was taking longer and EF runs migration first time a connection is requested once deployed was causing issue.

Since my table had data that could be regenerated (nothing critical to keep just some summary data) I quickly empties the table and ran migrations. Everything was good after that.