Skip to content

Instantly share code, notes, and snippets.

  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
What would you like to do?
Reworked version of Pieter Vanhove's Azure SQL Database reindexing Azure Automation Runbook
Perform index maintenance
This runbook uses Azure Automation to perform reindexing and statistics maintenance of all databases on a target server.
As prerequisite, please create an Azure Automation credential asset that contains the username and password for the target Azure SQL DB logical server ($SqlServerName).
Make sure that you have installed the scripts IndexOptimize.sql and CommandExecute.sql of Ola Hallengren (
Make sure to get Ola's modified scripts which work on Azure here:
AUTHOR: Original author Pieter Vanhove:
Heavily modified by Larry Silverman, CTO, TrackAbout
workflow SQLServerIndexMaintenance
param (
# Fully-qualified name of the Azure DB server
[string] $SqlServerName,
# Credentials for $SqlServerName stored as an Azure Automation credential asset
# When using in the Azure Automation UI, please enter the name of the credential asset for the "Credential" parameter
[PSCredential] $Credential
inlinescript {
# Set up credentials
$ServerName = $Using:SqlServerName
$UserId = $Using:Credential.UserName
$Password = ($Using:Credential).GetNetworkCredential().Password
$databases = @()
# Create connection to Master DB
Try {
$MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
$MasterDatabaseConnection.ConnectionString = "Server = $ServerName; Database = Master; User ID = $UserId; Password = $Password;"
# Create command to query the name of active databases in $ServerName
$MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
$MasterDatabaseCommand.Connection = $MasterDatabaseConnection
$MasterDatabaseCommand.CommandText =
select name from sys.databases
where state_desc='ONLINE'
and name <> 'master'
$MasterDbResult = $MasterDatabaseCommand.ExecuteReader()
while($MasterDbResult.Read()) {
$databases += @($MasterDbResult[0].ToString())
# Catch errors connecting to master database.
Catch {
Write-Error $_
Finally {
if ($null -ne $MasterDatabaseConnection) {
# Create connection for each individual database
# Iterate through each database under $ServerName
foreach ($DbName in $databases) {
Try {
# Setup connection string for $DbName
$ChildDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
$ChildDatabaseConnection.ConnectionString = "Server=$ServerName; Database=$DbName; User ID=$UserId; Password=$Password;"
# Create command for a specific database $DBName
$DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
$DatabaseCommand.Connection = $ChildDatabaseConnection
Write-Output "Performing index and statistics maintenance on $DbName"
# ExampleTable is a place holder for a table that holds a large volume of less important and expendable data
# that can be truncated to save space on the database.
$DatabaseCommand.CommandText ="
EXECUTE dbo.IndexOptimize
@Databases = '" + $DbName + "',
@FragmentationLow = NULL,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
$DatabaseCommand.CommandTimeout = 0
#Write-Output $DatabaseCommand.CommandText
$NonQueryResult = $DatabaseCommand.ExecuteNonQuery()
# Inner catch for individual database failures.
# We want to keep processing the next database.
Catch {
Write-Error $_
Finally {
if ($null -ne $ChildDatabaseConnection)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment