Firstly, great script. Secondly, the 3 hours fair share policy of automation accounts is really killing us.
We are running the following in a sandbox automation account against an Azure SQL server that has 31 databases that total 1.2TB in size. I had hoped that as the DB's get less fragmented, the time will get less to run this script. However, some of our larger DB's take hours on every run. Am I missing something here?
$Databases=Invoke-SqlCmd -ServerInstance $ServerInstance -AccessToken $AccessToken -Database master -Query "SELECT [name] AS [Database] FROM sys.databases WHERE name not in ('master') ORDER BY NEWID();"
$StopWatch=[system.diagnostics.stopwatch]::startNew()
foreach ($DB in $Databases)
{
$DBStopWatch=[system.diagnostics.stopwatch]::startNew()
Write-Output "Processing $($Databases.IndexOf($DB)+1) of $($Databases.Count) [$($DB.Database)]..."
Invoke-Sqlcmd -ServerInstance $ServerInstance -AccessToken $AccessToken -Database $DB.Database -Query "IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'AzureSQLMaintenance') BEGIN exec [dbo].[AzureSQLMaintenance] @Operation='all', @LogToTable=1, @ResumableIndexRebuild=1 END" -Verbose 4>&1
$DBStopWatch.Stop()
$DBStopWatch.Elapsed
}
Write-Output "Complete"
$StopWatch.Stop()
$StopWatch.Elapsed