Quick Scan Report – Ola Scripts Installed but Not Running

What this check looks for

Agent jobs belonging to Ola Hallengren’s SQL Server Maintenance Solution, checked for whether they are enabled, scheduled, and succeeding. The finding names the job and says which of those it fails.

The jobs it looks for are the ones the solution installs:

The check is skipped on Amazon RDS.

Why it matters

Somebody did the right thing and it is not working, which is more dangerous than never having done it.

Installing this solution is a deliberate, informed act. It is the standard answer to SQL Server maintenance, it is free, and choosing it means somebody knew what they were doing. That is precisely why it creates a false sense of security when the jobs stop:

• The jobs exist, so every inventory and every audit sees maintenance configured.
• The stored procedures exist in master, so a spot check finds the solution installed.
• Nobody looks again, because it is well known software that is famous for working.

And meanwhile, depending on which job it is:

• DatabaseBackup disabled means there are no backups. This is the one that matters. Every night it is off is a night added to your recovery point.
• DatabaseIntegrityCheck disabled means nothing is looking for corruption.
• IndexOptimize disabled is the least urgent and the most visible, because performance degrades gradually.

The usual causes are mundane. The jobs are created disabled by default when the solution is installed, and whoever installed it intended to schedule them later. Or they were disabled for a migration, a maintenance window or an incident, and never re-enabled. Or the job owner’s login was dropped and every one of them now fails at once.

How to confirm it yourself

SELECT j.[name]                                                       AS [job_name],
       j.[enabled],
       CASE WHEN s.[schedule_id] IS NULL THEN 'no schedule'
            WHEN s.[enabled] = 0 THEN 'schedule disabled'
            ELSE 'scheduled' END                                      AS [schedule_state],
       SUSER_SNAME(j.[owner_sid])                                     AS [owner],
       MAX(msdb.dbo.agent_datetime(h.[run_date], h.[run_time]))       AS [last_run],
       MAX(CASE WHEN h.[run_status] = 1
                THEN msdb.dbo.agent_datetime(h.[run_date], h.[run_time]) END) AS [last_success]
  FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
  LEFT JOIN msdb.dbo.sysjobschedules AS js WITH (NOLOCK) ON js.[job_id] = j.[job_id]
  LEFT JOIN msdb.dbo.sysschedules AS s WITH (NOLOCK) ON s.[schedule_id] = js.[schedule_id]
  LEFT JOIN msdb.dbo.sysjobhistory AS h WITH (NOLOCK)
         ON h.[job_id] = j.[job_id] AND h.[step_id] = 0
 WHERE j.[name] LIKE 'DatabaseBackup%'
    OR j.[name] LIKE 'DatabaseIntegrityCheck%'
    OR j.[name] LIKE 'IndexOptimize%'
    OR j.[name] LIKE 'CommandLog Cleanup%'
    OR j.[name] LIKE 'Output File Cleanup%'
 GROUP BY j.[name], j.[enabled], s.[schedule_id], s.[enabled], j.[owner_sid]
 ORDER BY j.[name];

Read three columns together: enabled, schedule_state and last_success. A job that is enabled and scheduled but whose last success is old is failing, which is a different fix from one that is simply switched off.

Confirm the solution is actually installed:

SELECT [name], [type_desc], [create_date], [modify_date]
  FROM master.sys.objects WITH (NOLOCK)
 WHERE [name] IN ('DatabaseBackup', 'DatabaseIntegrityCheck', 'IndexOptimize',
                  'CommandExecute', 'CommandLog')
 ORDER BY [name];

And what it has actually done, from its own log:

SELECT TOP (100) [DatabaseName], [CommandType], [StartTime], [EndTime], [ErrorNumber], [ErrorMessage]
  FROM master.dbo.CommandLog WITH (NOLOCK)
 ORDER BY [ID] DESC;

CommandLog is the authoritative record of what ran. An empty one on an instance where the solution has been installed for months is the finding stated plainly.

How to fix it

Enable and schedule the jobs, in priority order. Backups first, always:

EXEC msdb.dbo.sp_update_job @job_name = N'DatabaseBackup - USER_DATABASES - FULL',   @enabled = 1;
EXEC msdb.dbo.sp_update_job @job_name = N'DatabaseBackup - USER_DATABASES - LOG',    @enabled = 1;
EXEC msdb.dbo.sp_update_job @job_name = N'DatabaseBackup - SYSTEM_DATABASES - FULL', @enabled = 1;
EXEC msdb.dbo.sp_update_job @job_name = N'DatabaseIntegrityCheck - USER_DATABASES',  @enabled = 1;
EXEC msdb.dbo.sp_update_job @job_name = N'IndexOptimize - USER_DATABASES',           @enabled = 1;

Enabling is not the same as scheduling. The jobs are installed without schedules, so add one to each. A conventional shape:

Stagger them. Everything starting at midnight is its own finding on this report.

If the jobs are failing rather than disabled, check the owner first, since a dropped login takes every job out at once:

EXEC msdb.dbo.sp_update_job @job_name = N'DatabaseBackup - USER_DATABASES - FULL',
     @owner_login_name = N'sa';

Then set up notification, so the next lapse is an email rather than a scan finding. That is the jobs without failure notification check, and it is what turns this from a recurring discovery into a one-off.

And if there is also a maintenance plan doing the same work, decide which one owns it. Having both is how index maintenance ends up running twice and the window overruns.

How long it takes

About half an hour to enable and schedule. Catching up missed backups takes as long as the databases are large, and that is the part to start first.

Related reports

Related checks

Frequently asked questions

We use the procedures from our own jobs rather than the supplied ones. Then the supplied jobs being disabled is expected. Confirm your own jobs are running, and CommandLog tells you whether the procedures are being called at all.

Why are they installed disabled? That is the installer’s deliberate behaviour, so it does not start running maintenance on a server before anybody has chosen the schedules. It is also why this finding is common.

Do the cleanup jobs matter? Less, and they are worth enabling. CommandLog grows forever otherwise, and that has its own check.

We only use it for backups. Fine, and it is worth knowing that the integrity check job is sitting there disabled, because somebody may be assuming it runs.