Timeout expired. The timeout period elapsed prior to completion of the operation on Azure sql

Question

I need to create one sql database on windows azure on the global.asax application start event, however I got this error:

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=296; handshake=324; [Login] initialization=0; authentication=1; [Post-Login] complete=94;

My code is as follows:

   private void SetupSSM() {
            SqlConnectionStringBuilder connStrBldr = new SqlConnectionStringBuilder
            {
                UserID = SettingsHelper.AzureUsernamedb,
                Password = SettingsHelper.AzurePasswordDb,
                ApplicationName = SettingsHelper.AzureApplicationName,
                DataSource = SettingsHelper.AzureSqlServer
            };

            bool created=DbUtils.CreateDatabaseIfNotExists(connStrBldr.ConnectionString, SettingsHelper.Azureshardmapmgrdb);
            if(created)
            {
                Sharding sharding = new Sharding(SettingsHelper.AzureSqlServer, SettingsHelper.Azureshardmapmgrdb, connStrBldr.ConnectionString);
            }
        }



  public static bool CreateDatabaseIfNotExists(string connectionString, string databaseName)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand(
                    string.Format("SELECT * FROM sys.databases WHERE [name]=\'{0:S}\'", databaseName),
                    conn);

                if (cmd.ExecuteScalar() == null)
                {
                    SqlCommand cmd2 = new SqlCommand(
                        string.Format("CREATE DATABASE [{0:S}];", databaseName),
                        conn);

                    cmd2.ExecuteNonQuery();

                    return true;
                }
                else
                    return false;
            }
        }

How can I increase the timeout>? is it sql timeout or a web request timeout due to sql not responding?

I'm having the same problem here. Using Azure Search, and importing data from a SQL database. Always times-out after a while. Tried increasing the timeout and had no success. — Anoyz, Sep 13 '16 at 13:34

score 7 · Accepted Answer · answered Jul 14 '15 at 23:39

7

You can set the command timeout by doing the following, the error you are seeing is command timeout, most likely your create DB is taking longer than 30 seconds which is the default timeout value.

For example, set timeout to 300 seconds cmd.CommandTimeout = 300

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=vs.110).aspx

answered Jul 14 '15 at 23:39

Satya_MSFT

1,024
6
10

2

..Actually I am having the same issue, my SqlStatement.CommandTimeout is set to 900sec. and it still causes problems at times. It seems to be related to the DTUs and number of records in the Azure DB (db size). The BASIC level 5DTU 2GB DB option seems to be the worst. For some reason certain SPs and/or queries simply hang with no obvious cause even though they have been executed quickly many times before. I think it may be a process concurrency issue for the small Azure DBs. – Milan Feb 16 '16 at 00:04
1

You should check DTU usage and decide on the edition of the database – Satya_MSFT Feb 16 '16 at 03:02
1

yes, I agree but the BASIC level DTU(5) should perform as good as PREMIUM DTU level if the database size is kept within limits. – Milan Feb 16 '16 at 20:16
DTU has other factors apart from size of the database, like available memory, cpu etc. This is based on transaction per seconds – Satya_MSFT Feb 16 '16 at 21:01
I really wish it would be so simple but for example right now I am deleting 100 records from the same table on several databases. On 13 databases it deleted with no issues, on 2 remaining ones it is still locked up. All DBs are BASIC 5DTU and there are no (user based) concurrent processes running on any of the DBs or the root server. The DTU % on both "frozen" DBs is around 1.79% and the DB size 823MB. – Milan Feb 16 '16 at 22:33
I have the same problem using the Basic level database. Any resolution? – Nash Mar 18 '16 at 04:56

score 4 · Answer 2 · edited May 23 '17 at 11:45

4

When we had this problem, it turns out our database was under-powered. The performance monitor showed that the DTU's were maxed out during the time we were getting the errors. Azure SQL Database Operation Timeout

edited May 23 '17 at 11:45

Community

1
1

answered Jun 24 '16 at 15:08

jowensboggs

145
6

score 0 · Answer 3 · answered Oct 19 '21 at 10:31

As addition to @Satya_MSFTs explanation, you can add Command Timeout to a connection string, if your driver supports it. That could look something like this (in ADO.NET syntax):

_connectionString = "Server=tcp:myenv.database.windows.net,1433;Database=mydb;User ID=myuserid;Password=mypassword;Encrypt=true;Connection Timeout=120;Command Timeout=120;"

Where it can then be used in for instance inMicrosoft.Data.SqlClient.SqlConnection like this:

await using var conn = new SqlConnection(_connectionString);

Like others have said, you should try tuning your database before you go this way. (Not in OPs case. If it it a one-time setup operation it's fine to increase the timeout for that and then decrease it afterwards imo)

Keyword `Command Timeout` not supported – hiFI Jul 17 '23 at 07:29 — hiFI, Jul 17 '23 at 07:29

Timeout expired. The timeout period elapsed prior to completion of the operation on Azure sql

3 Answers3

Linked