Part of the DBA-Tools Project.
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
sawhen 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.
The inline script for this post is being moved to a GitHub Gist embed; in the meantime, view the full script directly on GitHub: Get-SqlAgentJobOverview.sql.
The script joins the SQL Agent jobs system table 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.sqlpowershell/wrappers/monitoring/jobs/Get-SqlAgentJobOverview.ps1
Example Output
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. In the run above, DBA Index Stats Maintenance shows Failed as its last outcome, an example of exactly the kind of thing this script is meant to surface before it’s discovered the hard way.
Understanding the Results
- enabled —
0means the job exists but will never run on its schedule. Worth confirming that’s deliberate. - owner_name — jobs owned by
sawill keep running even if the DBA who created them leaves, but jobs owned by a named login stop running (or error) if that login is disabled or dropped. Prefer a dedicated service account over a personal login. - last_run_outcome —
Failedis the one to act on immediately.RetryandCancelledare worth a look too; a job stuck retrying every run is functionally the same as a job that’s failing. - last_run_duration — a sudden jump in duration for a job that normally finishes in seconds is often the first sign of a growing problem (index maintenance taking longer as fragmentation grows, a backup job slowing down as the database grows).
A job with no rows in the job-server tracking table at all has never run since it was created, which is its own kind of red flag.
Related Scripts
You may also find these scripts useful:
- Agent Alerts and Operators
- Job Duration Trends
- Job Schedule Summary
- SQL Agent Job Failure Summary
Frequently Asked Questions
Why does a job show no last run date at all?
It has never executed since it was created, either because it’s disabled, its schedule hasn’t triggered yet, or it was created without a schedule and is meant to be run manually.
Does this script show job step details or just the overall outcome?
Just the overall outcome. For step-by-step failure detail on jobs that have failed, review the job history directly in SSMS or extend this query to join the SQL Agent job history table.
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.
Leave a Reply