Quick Scan Report – Agent Job Active But Not Scheduled

What this check looks for

Jobs in msdb.dbo.sysjobs with enabled = 1 that have no row in msdb.dbo.sysjobschedules. The message names the job. The check is skipped on Amazon RDS.

Why it matters

The job list is where people look to answer “what maintenance runs on this server”. A job that is enabled but unscheduled makes that list lie.

That is the whole of the problem, and it is a real one:

• Somebody reviews the job list, sees “Nightly Full Backup”, and concludes backups are running. It is enabled. It looks active. It has no schedule and has never run.
• A handover document lists it. The next person inherits a belief rather than a fact.
• A monitoring check that counts failed jobs reports nothing, because a job that never runs never fails.

The last point is the sharp one. Absence of failure is not evidence of success, and an unscheduled job is the purest example: it will never appear in any failure report, forever.

The legitimate reasons a job has no schedule are worth knowing, because several of them are perfectly fine:

• It is started by something else. Another job calls sp_start_job, an application triggers it, or a scheduling tool outside SQL Server drives it. This is common and correct.
• It is started by an alert. A WMI or performance condition alert can run a job as its response.
• It is a manual tool. A job kept for a DBA to run on demand, such as a restore or a cleanup.
• It is called by a Service Broker activation or a maintenance plan.

And the ones that are findings:

• The schedule was deleted rather than disabled, by accident. This is the dangerous one when the job is a backup or a CHECKDB.
• It was created and never finished. Somebody set it up intending to add a schedule and was interrupted.
• It is left over from a decommissioned process and should have been removed.

The distinction is entirely about intent, and intent lives in the job description, which is where the fix mostly lands.

How to confirm it yourself

Enabled jobs with no schedule:

SELECT j.[name]                     AS [job_name],
       j.[enabled],
       j.[description],
       SUSER_SNAME(j.[owner_sid])   AS [owner],
       j.[date_created],
       j.[date_modified],
       c.[name]                     AS [category]
  FROM msdb.dbo.sysjobs         AS j
  LEFT JOIN msdb.dbo.syscategories AS c ON c.[category_id] = j.[category_id]
 WHERE j.[enabled] = 1
   AND NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobschedules AS js
                    WHERE js.[job_id] = j.[job_id])
 ORDER BY j.[name];

Whether it has ever run, and when, which is the column that separates “driven externally” from “forgotten”:

SELECT j.[name] AS [job_name],
       MAX(msdb.dbo.agent_datetime(h.[run_date], h.[run_time])) AS [last_run],
       COUNT(*) AS [history_entries]
  FROM msdb.dbo.sysjobs        AS j
  LEFT JOIN msdb.dbo.sysjobhistory AS h ON h.[job_id] = j.[job_id] AND h.[step_id] = 0
 WHERE j.[enabled] = 1
   AND NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobschedules AS js WHERE js.[job_id] = j.[job_id])
 GROUP BY j.[name]
 ORDER BY [last_run];

A job with recent runs and no schedule is being started by something, and that is the answer. A job with no history at all has never run since the history was last purged, and that is the finding.

What starts it, if something does. Look for sp_start_job calls elsewhere:

-- other jobs that start this one
SELECT j.[name] AS [calling_job], st.[step_name], st.[command]
  FROM msdb.dbo.sysjobsteps AS st
 INNER JOIN msdb.dbo.sysjobs AS j ON j.[job_id] = st.[job_id]
 WHERE st.[command] LIKE '%sp_start_job%';

-- alerts that run a job as their response
SELECT a.[name] AS [alert_name], j.[name] AS [job_run], a.[enabled]
  FROM msdb.dbo.sysalerts AS a
 INNER JOIN msdb.dbo.sysjobs AS j ON j.[job_id] = a.[job_id];

And the inverse problem, which is worth checking at the same time: jobs with a schedule that is disabled, which look scheduled and are not.

SELECT j.[name] AS [job_name], s.[name] AS [schedule_name],
       j.[enabled] AS [job_enabled], s.[enabled] AS [schedule_enabled]
  FROM msdb.dbo.sysjobs           AS j
 INNER JOIN msdb.dbo.sysjobschedules AS js ON js.[job_id] = j.[job_id]
 INNER JOIN msdb.dbo.sysschedules    AS s  ON s.[schedule_id] = js.[schedule_id]
 WHERE s.[enabled] = 0
 ORDER BY j.[name];

That combination is more misleading than having no schedule at all, because the job list shows a schedule.

How to fix it

Decide which category each job is in, then make the job say so.

If it should be scheduled, add the schedule. This is the urgent case when the job is a backup, a CHECKDB or an index maintenance job:

EXEC msdb.dbo.sp_add_jobschedule
     @job_name          = N'Nightly Full Backup',
     @name              = N'Daily 01:00',
     @freq_type         = 4,          -- daily
     @freq_interval     = 1,
     @active_start_time = 010000;

If it is started by something else, leave it and say so in the description, which is the fix for the confusion this check is about:

EXEC msdb.dbo.sp_update_job
     @job_name    = N'Rebuild Reporting Tables',
     @description = N'No schedule by design. Started by the ETL job "Nightly Load" '
                  + N'step 4 via sp_start_job. Do not add a schedule.';

If it is a manual tool, say that too, and consider disabling it so its state matches its purpose:

EXEC msdb.dbo.sp_update_job
     @job_name    = N'Restore Prod To Test',
     @description = N'Run on demand only. Not scheduled deliberately.',
     @enabled     = 0;

A disabled job is honest about not running. An enabled one with no schedule is not, and that is the single most useful change this check prompts.

If it is obsolete, script it out and remove it:

-- keep the definition first
SELECT j.[name], st.[step_id], st.[step_name], st.[subsystem], st.[command]
  FROM msdb.dbo.sysjobs AS j
 INNER JOIN msdb.dbo.sysjobsteps AS st ON st.[job_id] = j.[job_id]
 WHERE j.[name] = N'Old Process';

EXEC msdb.dbo.sp_delete_job @job_name = N'Old Process';

Then use job categories, which makes the whole list readable at a glance and costs nothing:

EXEC msdb.dbo.sp_add_category @class = N'JOB', @type = N'LOCAL',
                              @name = N'Manual Tools';

EXEC msdb.dbo.sp_update_job @job_name = N'Restore Prod To Test',
                            @category_name = N'Manual Tools';

With categories set, the job list distinguishes scheduled maintenance from on demand tools without anybody having to open each job.

How long it takes

About half an hour for an instance, most of it establishing which jobs are driven externally.

Related reports

Related checks

Frequently asked questions

Is a job without a schedule always wrong? No. Plenty are started by other jobs, by alerts, or by an external scheduler. The fix for those is to record that in the job description so the next person does not have to work it out.

Why not just disable them? Disable the ones that are manual tools, because that makes the state honest. Do not disable one that is started by another job, because a disabled job cannot be started by sp_start_job either.

How do I find what starts a job? Search other job steps for sp_start_job, check msdb.dbo.sysalerts for alerts that run it, and look in application code and any external scheduler. If nothing turns up and it has run recently, the run history timestamps will usually match something.

The job has a schedule but still never runs. Check whether the schedule itself is disabled, which is the query above. That combination is more misleading than having no schedule at all.