DBA Scripts: Get SQL Agent Job Overview

Most SQL Server instances end up with a handful of Agent jobs that quietly run backups, index maintenance, and monitoring collectors in the background. Nobody looks at them until something breaks, and by then it can be hard to tell whether a job has been failing for a day or for three months. A job that fails silently every night, with no alert wired up and nobody checking, is one of the most common ways a “we thought we had backups” incident happens.

This script gives a single-glance overview of every SQL Agent job on the instance: whether it’s enabled, who owns it, and how its last run actually went.

Why SQL Agent Job Overview Matters

SQL Agent is the scheduler behind most of the routine work that keeps a database healthy: backups, index and statistics maintenance, log shipping, replication agents, and any custom collector jobs. A job that’s disabled, orphaned, or silently failing doesn’t throw an error anyone sees unless alerting is specifically configured for it.

This overview surfaces the things that matter operationally:

• Jobs that are disabled and no longer running at all
• Jobs owned by a login that no longer exists, or by sa when it shouldn’t be
• The outcome of each job’s last run: Succeeded, Failed, Retry, or Cancelled
• How long the last run actually took

When to Run This Script

• Routine SQL Server health checks
• After inheriting or migrating a server, to see what’s actually scheduled
• Investigating a backup or maintenance gap
• Auditing job ownership before decommissioning a login or service account

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-SqlAgentJobOverview
Category    : configuration-and-environment
Purpose     : Show all SQL Agent jobs with enabled state, owner, and last run outcome.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-sql-agent-job-overview/)
Requires    : db_datareader on msdb, plus VIEW ANY DEFINITION to resolve job owner names (the job rows return either way; an owner only resolves if that login is visible to you)
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    j.name AS job_name,
    j.enabled,
    j.description,
    ISNULL(sp.name, '(unknown)') AS owner_name,
    j.date_created,
    j.date_modified,
    CASE js.last_run_outcome
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        ELSE 'Unknown'
    END AS last_run_outcome,

    -- Last run timestamp (last_run_date is yyyymmdd int, last_run_time is HHmmss int;
    -- both are 0 for a job that has never run)
    CASE WHEN js.last_run_date = 0 OR js.last_run_date IS NULL THEN NULL
         /* Inline conversion - agent_datetime() is a scalar UDF that db_datareader
            cannot EXECUTE, so using it would break the stated Requires */
         ELSE CONVERT(DATETIME,
                  STUFF(STUFF(CAST(js.last_run_date AS CHAR(8)), 7, 0, '-'), 5, 0, '-') + ' ' +
                  STUFF(STUFF(RIGHT('000000' + CAST(js.last_run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':'))
    END AS last_run_at,

    -- Duration formatted as HH:MM:SS (last_run_duration is an HHMMSS-packed int)
    CASE WHEN js.last_run_date = 0 OR js.last_run_date IS NULL THEN NULL
         ELSE RIGHT('0' + CAST(js.last_run_duration / 10000 AS varchar(4)), 2) + ':'
            + RIGHT('0' + CAST((js.last_run_duration % 10000) / 100 AS varchar(2)), 2) + ':'
            + RIGHT('0' + CAST(js.last_run_duration % 100 AS varchar(2)), 2)
    END AS last_run_duration
FROM msdb.dbo.sysjobs AS j
LEFT JOIN sys.server_principals AS sp ON j.owner_sid = sp.sid
LEFT JOIN msdb.dbo.sysjobservers AS js ON j.job_id = js.job_id
ORDER BY j.name;

The script joins msdb.dbo.sysjobs to the job’s owner and its last recorded run status, returning one row per Agent job.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# List every SQL Agent job with owner and last run outcome:
.\run.ps1 Get-SqlAgentJobOverview

# To run against a remote sql server:
.\run.ps1 Get-SqlAgentJobOverview -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/monitoring/jobs/Get-SqlAgentJobOverview.sql
• powershell/wrappers/monitoring/jobs/Get-SqlAgentJobOverview.ps1

Example Output

Two rows in this capture tell the story on their own. The disabled FULL and LOG backup jobs show Unknown with blank run columns, meaning they have never executed, and the Query Store collector shows Failed with a 07:55:06 runtime against siblings that finish in seconds, both worth chasing before anything else on the list.

The columns you actually need are enabled, owner_name, and last_run_outcome; the rest is context for when one of those three looks wrong. The run timestamps come back as a real last_run_at datetime and the duration as HH:MM:SS, rather than the raw integer formats msdb stores internally, so the output reads at a glance. A job that has never run shows Unknown with both columns blank.

Understanding the Results

A job with no rows in msdb.dbo.sysjobservers at all has never run since it was created, which is its own kind of red flag.

Microsoft’s reference covers dbo.sysjobs and dbo.sysjobservers in full.

Related Scripts

You may also find these scripts useful:

• Agent Alerts and Operators
• Job Duration Trends
• Job Schedule Summary
• SQL Agent Job Failure Summary
• DBA Scripts: Get Maintenance Job Status
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Summary

A SQL Agent job that silently stopped succeeding is one of the quieter ways a “we have backups” assumption turns out to be wrong. This script gives a fast way to confirm every scheduled job is enabled, correctly owned, and actually succeeding, rather than waiting to find out during a restore.

Run this script as part of your regular SQL Server health checks, and again any time you inherit a server, to confirm what’s actually scheduled matches what you expect to be running.