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?

Solution 1

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

Solution 2

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