Ola Hallengren Maintenance Solution

EXECUTE [dbo].[DatabaseBackup]

@Databases = 'SYSTEM_DATABASES',

@Directory = NULL,

@BackupType = 'FULL',

@Verify = 'Y',

@CleanupTime = 48,

@CheckSum = 'Y',

@LogToTable = 'Y'

EXECUTE [dbo].[DatabaseBackup]

@Databases = 'USER_DATABASES',

@Directory = NULL,

@BackupType = 'FULL',

@Verify = 'Y',

@CleanupTime = 27,

@CheckSum = 'Y',

@LogToTable = 'Y'

EXECUTE [dbo].[DatabaseBackup]

@Databases = 'USER_DATABASES',

@Directory = NULL,

@BackupType = 'LOG',

@Verify = 'Y',

@CleanupTime = 50,

@CheckSum = 'Y',

@LogToTable = 'Y'

DatabaseIntegrityCheck

DatabaseIntegrityCheck - SYSTEM_DATABASES

• Schedule at least weekly

EXECUTE [dbo].[DatabaseIntegrityCheck]

@Databases = 'SYSTEM_DATABASES',

@LogToTable = 'Y'

DatabaseIntegrityCheck - USER_DATABASES

• Schedule at least weekly

EXECUTE [dbo].[DatabaseIntegrityCheck]

@Databases = 'USER_DATABASES',

@LogToTable = 'Y'

IndexOptimize - USER_DATABASES

• Run weekly (or less frequently).

• Don’t use defaults. Consider 50% for REORGANIZE, 80% or even 90% for REBUILD (1).

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 50,

@FragmentationLevel2 = 80,

@LogToTable = 'Y'

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = NULL,

@FragmentationHigh = NULL,

@UpdateStatistics = 'ALL',

@OnlyModifiedStatistics = 'Y',

@LogToTable = 'Y';

Housekeeping Jobs

CommandLog Cleanup

Output File Cleanup

sp_delete_backuphistory

sp_purge_jobhistory

syspolicy_purge_history

Check

SELECT sysjobs.name               job_name,

       sysjobs.enabled            job_enabled,

       sysschedules.name          schedule_name,

       sysschedules.enabled       schedule_enabled,

       sysschedules.schedule_id,

       sysjobschedules.next_run_date,

       sysjobschedules.next_run_time,

  "Freq" = CASE sysschedules.freq_type

    WHEN '1' THEN 'Once'

WHEN '4' THEN 'Daily'

WHEN '8' THEN 'Weekly'

WHEN '16' THEN 'Monthly'

WHEN '32' THEN 'Monthly (relative to freq_interval)'

WHEN '64' THEN 'At Agent Start'

WHEN '128' THEN 'When Idle'

ELSE 'Unexpected'

  END,

  sysschedules.freq_interval,

  sysschedules.freq_subday_type,

  sysschedules.freq_subday_interval,

  sysschedules.freq_recurrence_factor,

  sysschedules.active_start_time

  FROM msdb.dbo.sysjobs

 INNER JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id

 INNER JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id

 WHERE sysjobs.name = 'IndexOptimize - USER_DATABASES'

 ORDER BY sysjobs.enabled desc

USE [msdb]

SELECT sj.name JobName,

       sj.enabled,

       sj.start_step_id,

       sjs.step_id,

       sjs.step_name,

       sjs.subsystem,

       sjs.command,

       CASE on_success_action

          WHEN 1 THEN 'Quit with success'

          WHEN 2 THEN 'Quit with failure'

          WHEN 3 THEN 'Go to next step'

          WHEN 4 THEN 'Go to step ' + CAST(on_success_step_id AS VARCHAR(3))

       END On_Success,

       CASE on_fail_action

          WHEN 1 THEN 'Quit with success'

          WHEN 2 THEN 'Quit with failure'

          WHEN 3 THEN 'Go to next step'

          WHEN 4 THEN 'Go to step ' + CAST(on_fail_step_id AS VARCHAR(3))

       END On_Failure

  FROM dbo.sysjobs sj

 INNER JOIN dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id

 WHERE sj.name = 'DatabaseIntegrityCheck - SYSTEM_DATABASES'

Bibliography