Finding Old Agent Jobs Not Run in Months or Never Scheduled

Open the job list and ask which entries still serve an owner. Finding agent jobs not run recently creates a focused review queue. Combine retained history, activity records, enabled state, and schedule presence before asking an owner whether a job should remain active.

Inventory Agent Jobs Not Run Before Cleaning the List

SQL Server Agent definitions can outlive the applications and processes that created them. Some are retired, while others are occasional recovery or manual operations. The absence of a recent scheduled run is therefore a candidate condition, not a deletion instruction.

I ask who owns the job and what business work it performs before changing enabled state. The name and description can help, but they are not guaranteed to be current. Review the steps, schedules, and surrounding operating procedure.

History retention limits the evidence. msdb job history can be purged, and activity records describe a different source of execution context. A job with no retained history did not necessarily never run. The inventory should say no observed run in the available records, with that limitation visible. Old jobs are like spare cables: some are useless, and one usually belongs to the thing you forgot to check.

Review agent jobs not run against the available history and scheduling purpose before marking them as retirement candidates.

Combine Outcome History With Activity Starts

sysjobhistory step_id equal to zero represents the job outcome row. Convert its run_date and run_time through msdb.dbo.agent_datetime only for valid retained dates. sysjobactivity can supply start times not represented in the retained outcome rows, including incomplete or more recent activity.

The following query computes the latest available timestamp from both sources and counts schedule links. Its synthetic cutoff of ninety days is a review setting. Change it to match your operational policy. The query includes enabled state and owner for the candidate discussion.

I keep the source dates visible when validating this inventory. A latest activity start is not proof of successful completion. A history outcome timestamp is not a guarantee that all downstream business work remained valid. The purpose is to find candidates with limited recent activity, then inspect what those observations mean before acting.

SELECT j.job_id,j.name,j.enabled,SUSER_SNAME(j.owner_sid) AS JobOwner,
 h.LastOutcomeRun,a.LastActivityStart,k.LastObservedRun,s.ScheduleLinks
FROM msdb.dbo.sysjobs AS j
OUTER APPLY
(SELECT MAX(msdb.dbo.agent_datetime(run_date,run_time)) AS LastOutcomeRun
 FROM msdb.dbo.sysjobhistory WHERE job_id=j.job_id AND step_id=0 AND run_date>0) AS h
OUTER APPLY
(SELECT MAX(start_execution_date) AS LastActivityStart
 FROM msdb.dbo.sysjobactivity WHERE job_id=j.job_id) AS a
OUTER APPLY
(SELECT MAX(d.RunTime) AS LastObservedRun FROM(VALUES(h.LastOutcomeRun),(a.LastActivityStart)) AS d(RunTime)) AS k
OUTER APPLY
(SELECT COUNT_BIG(*) AS ScheduleLinks FROM msdb.dbo.sysjobschedules WHERE job_id=j.job_id) AS s
WHERE k.LastObservedRun IS NULL OR k.LastObservedRun<DATEADD(day,-90,GETDATE()) OR s.ScheduleLinks=0
ORDER BY k.LastObservedRun,j.name;

Distinguish No Schedule From No Use

A job without a schedule can be a valid manually invoked operation. Another job or an application can call it explicitly. A linked schedule can also be disabled, expired, or configured for a special startup condition. Schedule presence alone does not prove normal automatic execution.

The next query lists linked schedule definitions and their enabled state. Review the frequency information through the supported Agent schedule interface when the numeric fields need interpretation. Do not invent a human-readable calendar from a partial field mapping.

Which trigger is supposed to start the job? Record that answer for each candidate. A manual recovery job and an abandoned nightly job need different responses. Also check whether Agent itself has been stopped or unable to run work. A lack of recent execution can indicate a service problem rather than a retired definition. The inventory should not reward a broken scheduler by deleting its workload.

SELECT j.name AS JobName,s.name AS ScheduleName,s.enabled,
       s.freq_type,s.freq_interval,s.active_start_date,s.active_end_date,
       s.active_start_time,s.active_end_time
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobschedules AS js ON js.job_id=j.job_id
LEFT JOIN msdb.dbo.sysschedules AS s ON s.schedule_id=js.schedule_id
ORDER BY j.name,s.name;

Inspect the Steps and Recent Outcomes

Read the command, subsystem, execution database, and step sequence before making a decision. A harmless-looking job name can contain a required maintenance or data-processing action. External dependencies and output files can also explain why it remains in the operating process.

The next query shows recent job-level outcomes for one selected job. Replace the placeholder name with a reviewed candidate. run_status and message give useful completion context. Keep the original numeric status alongside its documented interpretation when preparing a formal review.

Do not select only successful outcomes and then conclude the job was unused. Repeated failures still indicate attempted work and a possible operational issue. Equally, a successful outcome does not prove the current definition remains required. Pair retained execution evidence with the owner's explanation of the job's purpose and the process that would be affected by disabling it.

SELECT TOP(20) h.run_date,h.run_time,h.run_duration,h.run_status,h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id=h.job_id
WHERE j.name=N'ReviewedJobName' AND h.step_id=0
ORDER BY h.instance_id DESC;

Disable an Approved Candidate Before Deleting It

Disabling preserves the definition and provides a reversible observation period. It is a better first action for an approved retirement candidate than deleting its steps and schedules immediately. Keep a record of the reason, owner, and date of the decision.

The next statement is an action example for a job already approved for disabling. It does not perform an inventory-wide cleanup. Verify the exact job name and retain its definition through the review window. Disabling a job does not automatically stop an execution already running, so inspect activity separately when that matters.

Watch for callers or procedures that still expect the job. A disabled definition can expose an overlooked dependency, which is the reason to preserve a straightforward restoration path. Set enabled back to one only after confirming the intended schedule and surrounding process. Reversibility is useful when it preserves a reviewed decision, not when it encourages casual changes without an owner.

EXEC msdb.dbo.sp_update_job @job_name=N'ReviewedJobName',@enabled=0;

Recheck Agent Jobs Not Run on a Regular Cycle

Run the inventory periodically with the same retained-history assumptions and cutoff policy. Track reviewed exceptions so the same required manual jobs do not generate an endless unexplained list. Refresh those exceptions when ownership or purpose changes.

If retention is too short to answer the operational question, preserve an approved job-run history outside the default purge window. Do not fill missing dates with guessed execution times. Missing evidence should remain explicit until another source supports the conclusion.

Agent jobs not run recently deserve a human-readable purpose review backed by catalog evidence. Combine history and activity, inspect schedules and steps, and disable only approved candidates. A cleaner job list is useful when it still contains every required operation and explains why the remaining exceptional jobs are there.

Related reading on this blog: Agent Jobs Running Longer Than Usual: Finding Them in Job History and Details About SQL Jobs and Job Schedules.

An empty job history is not proof of retirement, it is missing execution evidence that needs an owner.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.