Scaling Azure SQL Database fails with OperationTimedOut when initiated from Automation runbook

Question

I have a runbook to scale up/down the SQL Database DTUs on a schedule. However, for my production database; whenever this runbook executes, it fails with a timeout. Manually changing the Database DTUs is successful; but when it's initiated by the automation, I can see this message posted in the Activity Log:

{\"status\":\"Failed\",\"error\":{\"code\":\"ResourceOperationFailure\",\"message\":\"The resource operation completed with terminal provisioning state 'Failed'.\",\"details\":[{\"code\":\"OperationTimedOut\",\"message\":\"The operation timed out and automatically rolled back. Please retry the operation.\"}]}}

Executing the same runbook script against the UAT database is successful.

Answer 1

Try to the following PowerShell script. It has never failed on my environment.

# Define the SQL command to run
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 120
$sqlCommand.Connection = $sqlConnection
Write-Output "Issuing command to scale down database"
# Execute the SQL command
$sqlCommand.CommandText= "ALTER DATABASE [YourDatabase] MODIFY (EDITION = 'Premium', MAXSIZE = 1024 GB, SERVICE_OBJECTIVE = 'P6');"
$result = $sqlCommand.ExecuteNonQuery()