Commands / New-sqmOlaMaintenanceJobs
Performance

New-sqmOlaMaintenanceJobs

Creates three Ola Hallengren maintenance jobs, IndexOptimize (USER_DATABASES), IntegrityCheck (USER_DATABASES), and IntegrityCheck (SYSTEM_DATABASES), with extensive fragmentation and CHECKDB tuning defaults.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: Yes
Output: PSCustomObject

Execution Flow

START dbatools available? throw error Test-sqmOlaInstallation -RequiredSet Index Ola objects installed? Status=Failed, return Yes internal _CreateJob helper, called 3× New-DbaAgentJob + step + schedule (Sunday 23:00) Job 1: IndexOptimize @Databases='USER_DATABASES' Job 2: IntegrityCheck @Databases='USER_DATABASES' Job 3: IntegrityCheck @Databases='SYSTEM_DATABASES' Return PSCustomObject (OverallStatus per-job) DONE
Requires Ola Hallengren's maintenance solution objects to already be installed (checked via Test-sqmOlaInstallation -RequiredSet Index). Job names are resolved from Get-sqmConfig (OlaJobNameIndexOpt, OlaJobNameIntUserDb, OlaJobNameIntSysDb). OverallStatus can be PartialSuccess if one of the three jobs fails while others succeed.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-FragmentationLevel1IntOptionalReorganize threshold (%). Default: 5.
-FragmentationLevel2IntOptionalRebuild threshold (%). Default: 30.
-MinNumberOfPagesIntOptionalMinimum index size considered. Default: 1000.
-FillFactorIntOptionalRebuild fill factor. Default: 90.
-MaxDOPIntOptionalMax degree of parallelism. Default: 0 (server default).
-SortInTempdbStringOptionalY/N. Default: Y.
-UpdateStatisticsStringOptionalALL / COLUMNS / INDEX. Default: ALL.
-CheckCommandsStringOptionalOla CheckCommands. Default: CHECKDB.
-PhysicalOnlyStringOptionalY/N. Default: N.
-NoIndexStringOptionalY/N. Default: N.
-ScheduleTimeStringOptionalDefault: 23:00.
-ScheduleDayIntOptionalSQL Agent weekday bitmask. Default: 1 (Sunday).
-EnableExceptionSwitchOptionalThrows terminating errors instead of returning an error status object.
-WhatIf / -ConfirmSwitchOptionalStandard ShouldProcess support.
-ContinueOnErrorswitchOptionalContinue with the next job on error (rarely used).
-DatabasesstringOptionalDatabase filter for IndexOptimize and IntegrityCheck user. Default: 'USER_DATABASES'.
-JobCategorystringOptionalCategory for all three jobs. Default: 'Database Maintenance'.
-JobNameIndexOptstringOptionalName of the IndexOptimize job (overrides module configuration).
-JobNameIntSysDbstringOptionalName of the IntegrityCheck job for system DBs (overrides module configuration).
-JobNameIntUserDbstringOptionalName of the IntegrityCheck job for user DBs (overrides module configuration).
-LogToTablestringOptionalOla internal logging to CommandLog table. Default: 'Y'.
-OnlyModifiedStatisticsstringOptionalOnly update modified statistics. Default: 'Y'.
-OperatorNamestringOptionalSQL Agent operator for email notification on failure.
-StatisticsSampleintOptionalSample size for statistics update in percent. Default: 0 (SQL Server default).
-UpdateswitchOptionalReplace existing jobs with the same name.

Return Value

Returns a PSCustomObject with: SqlInstance, JobNameIndexOpt, JobNameIntUserDb, JobNameIntSysDb, IndexOptStatus, IntUserDbStatus, IntSysDbStatus, OverallStatus (PartialSuccess / WhatIf / Success / Failed), Message.

Examples

Example 1, Create all three jobs with defaults

New-sqmOlaMaintenanceJobs -SqlInstance "SQL01"

Example 2, Custom fragmentation thresholds

New-sqmOlaMaintenanceJobs -SqlInstance "SQL01" -FragmentationLevel1 10 -FragmentationLevel2 40 -MaxDOP 4
⬆ Index
All Commands