Update Ola Hallengren’s IndexOptimize Job to only update statistics

Posted by cviorel on May 11, 2020 Leave a comment (0) Go to comments

Managing a lot of SQL servers does not have to to be a burden. One or a hundred, should be the same, right?
This is how you can quickly update (on a bunch of servers) the IndexOptimize job which is created if you install the MaintenenceSolution from Ola Hallengren:

$servers = @(
    'server_01',
    'server_02',
    'server_03',
    'server_04',
    'server_nn'

    )
$newCommand = @'
EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES' ,
@FragmentationLow = NULL ,
@FragmentationMedium = NULL ,
@FragmentationHigh = NULL ,
@UpdateStatistics = 'ALL' ,
@OnlyModifiedStatistics = N'Y' ,
@LogToTable = N'N';
'@

$ports = 10001..10005
foreach ($node in $servers) {
    foreach ($port in $ports) {
        $instance = "$node,$port"
        $jobName = 'IndexOptimize - USER_DATABASES'
        $job = Get-DbaAgentJob -SqlInstance "$instance" -Job $jobName
        foreach ($Step in $Job.jobsteps.Where{ $_.Name -eq $jobName }) {
            $Step.Command = $newCommand
            $Step.Alter()
        }
    }
}