CODEWITHSUNDEEP
sql-serverasp.net-mvcazureazure-sql-databaseazure-elastic-scale
Luis Valencia
Luis Valencia

Reputation: 33998

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

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?

Upvotes: 4

Views: 18711

Answers (3)

MyrionSC2
MyrionSC2

Reputation: 1318

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)

Upvotes: 0

jowensboggs
jowensboggs

Reputation: 145

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

Upvotes: 4

Satya_MSFT
Satya_MSFT

Reputation: 1022

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

Upvotes: 7

Related Questions