MSSQL Maintenance

Ola Hallengren Maintenance Solution

Install

Download and run the single install script. This will install default scheduled jobs (but will not schedule them). The default jobs may not be setup as shown below. Review and update as necessary....

DatabaseBackup

DatabaseBackup - SYSTEM_DATABASES - FULL

SELECT *   FROM sys.configurations WHERE name LIKE '%compression%'

EXECUTE [dbo].[DatabaseBackup]

@Databases = 'SYSTEM_DATABASES',

@Directory = NULL,

@BackupType = 'FULL',

@Verify = 'Y',

@CleanupTime = 48,

@CheckSum = 'Y',

@LogToTable = 'Y'

DatabaseBackup - USER_DATABASES - FULL

Consider the implications to your restore strategy of doing this. Also consider how this impacts any backup of these backup files to another backup tool. i.e. this works best if the other backup tool doesn't backup up the FULL and all DIFF backups every night but backs up only the files that have changed. It does mean restoring more files in order to perform any recovery though.
SELECT *   FROM sys.configurations WHERE name LIKE '%compression%'

EXECUTE [dbo].[DatabaseBackup]

@Databases = 'USER_DATABASES',

@Directory = NULL,

@BackupType = 'FULL',

@Verify = 'Y',

@CleanupTime = 27,

@CheckSum = 'Y',

@LogToTable = 'Y'

DatabaseBackup - USER_DATABASES - LOG

SELECT *   FROM sys.configurations WHERE name LIKE '%compression%'

EXECUTE [dbo].[DatabaseBackup]

@Databases = 'USER_DATABASES',

@Directory = NULL,

@BackupType = 'LOG',

@Verify = 'Y',

@CleanupTime = 50,

@CheckSum = 'Y',

@LogToTable = 'Y'

DatabaseIntegrityCheck

DatabaseIntegrityCheck - SYSTEM_DATABASES

EXECUTE [dbo].[DatabaseIntegrityCheck]

@Databases = 'SYSTEM_DATABASES',

@LogToTable = 'Y'

DatabaseIntegrityCheck - USER_DATABASES

EXECUTE [dbo].[DatabaseIntegrityCheck]

@Databases = 'USER_DATABASES',

@LogToTable = 'Y'

IndexOptimize

IndexOptimize - USER_DATABASES

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 50,

@FragmentationLevel2 = 80,

@LogToTable = 'Y'

UpdateStats - USER_DATABASES

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