How to List All the SQL Server Jobs When Agent is Disabled? – Interview Question of the Week #171

Question: How can I list SQL Server Agent jobs when the Agent service is stopped?

A developer in one of my training sessions asked this, and I had never thought to phrase the question that way. In SSMS, we normally expand SQL Server Agent, then Jobs. Stop the Agent service and that convenient route disappears. The jobs themselves have not disappeared.

Method one is to start the service, assuming it is meant to run. That takes the fun out of this question. Method two is a read-only query against msdb, where Agent stores job metadata.

SELECT j.job_id, j.name, j.enabled, j.description
FROM msdb.dbo.sysjobs AS j
ORDER BY j.name;

The enabled column tells you whether a job is configured to run. It does not tell you that the job is running now. Even an enabled job cannot execute on its schedule while Agent is stopped. The query deliberately names its columns instead of returning everything in sysjobs.

If you also need schedule names, use a left join. An inner join would silently omit jobs that have no attached schedule, which would be a poor answer to a question about all jobs.

SELECT j.name AS job_name,
       j.enabled AS job_enabled,
       s.name AS schedule_name,
       s.enabled AS schedule_enabled
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;

A job can have more than one schedule, so it can appear on more than one result row. A job without a schedule still appears, with a null schedule name. A disabled schedule is separate from the job’s own enabled flag. Avoid treating the cached next-run values in sysjobschedules as a real-time execution signal; Microsoft documents that they refresh periodically.

For past outcomes, msdb.dbo.sysjobhistory is a separate source. It may be empty because a job never ran or because old history was purged. Its date and time fields are stored as integers, so convert them before displaying a timestamp. Access to Agent metadata is controlled by SQL Server permissions.

The useful distinction is simple: the SSMS Agent tree is a way to browse jobs; msdb holds their metadata. The service can be down while the records remain available to an authorized reader.

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