SQL Server Job Status
UID:
sql_server_job_statusVersion: 1.3.1 Panels: ~14 Datasource: MSSQL
Purpose
This dashboard provides complete visibility of SQL Server Agent Job executions, including:
- Failed, cancelled, and long-running jobs
- Currently running jobs
- Historical execution data to identify trends
- Performance deviations and recurring issues
SQL Server Agent is the integrated task scheduling component in SQL Server that allows automating administrative tasks, database maintenance, and critical business processes. For more information, see the official SQL Server Agent documentation.
Variables
| Variable | Description |
|---|---|
$Server_Name | SQL Server to monitor |
$Job | Specific job or multiple jobs to filter |
Color Code
| Color | Code | Meaning |
|---|---|---|
| Green | #00db88 | Success - Successful execution |
| Magenta | #ad21d8e8 | To Check - Review |
| Red | #F2495C | Failed - Failed execution |
| Yellow | #FFFF00 | Running - In execution |
| Orange | #FF780A | Cancelled - Cancelled |
Panels
Running Jobs
Running Jobs logs
Jobs currently in execution.
Importance: Knowing the running jobs is fundamental to avoid resource conflicts. Multiple jobs running simultaneously can compete for CPU, memory, and I/O, degrading the overall server performance. It also helps identify jobs that should have finished but are still active.
Running Jobs Exceeding Duration
Jobs Exceed Normal Duration logs
Running jobs that exceed their usual duration.
Importance: A job that exceeds its normal duration is an early warning sign of underlying problems. It can indicate locks, resource contention, unexpected data growth, or performance degradation. Detecting these anomalies quickly allows intervention before they affect other processes or users.
Long-running jobs
A job exceeding its normal duration may indicate:
- Database locks
- Unexpected data growth
- Underlying performance issues
Job Executions Summary
Job Executions stat
Execution summary in the selected period.
Displayed metrics:
- Successful Executions: Number of successfully executed jobs
- Failed Executions: Number of failed jobs
- Cancelled Steps: Number of cancelled steps
- Failed Steps: Number of failed steps
Importance: This summary provides a quick view of operational health. Failed jobs can mean incomplete backups, broken ETL, or unexecuted maintenance. Each failure has potential impact on data integrity, business SLAs, or regulatory compliance.
Thresholds:
| Metric | Value | Color |
|---|---|---|
| Failed Executions | >= 1 | Red |
| Cancelled Steps | >= 1 | Magenta |
| Failed Steps | >= 1 | Magenta |
Jobs Enabled/Disabled
Jobs Enabled stat
Number of enabled jobs on the server.
Importance: Monitoring the number of enabled jobs helps detect unauthorized configuration changes. An accidentally disabled job can result in unperformed backups, unprocessed data, or unregenerated reports.
Jobs Disabled stat
Number of disabled jobs on the server.
Importance: Disabled jobs should be reviewed periodically. They may be obsolete jobs that should be deleted, or critical jobs temporarily disabled that were never reactivated. Maintaining a clean inventory improves system maintainability.
Job History Table
Job History table
Job execution history in the selected time period.
Columns:
- Job Name
- Step
- Status
- Execution Date
- Duration
Importance: Detailed history allows analyzing trends, identifying recurring failure patterns, and establishing normal duration baselines. It is essential for audits and for demonstrating SLA compliance.
Status mapping:
| Status | Color |
|---|---|
| Success | Green |
| Running | Yellow |
| Cancelled | Orange |
| Failed | Red |
Usually Failed Jobs
Usually Failed Jobs table
Jobs that fail frequently (recurring failure pattern).
Importance: Jobs with recurring failures represent technical debt that must be addressed. Each failure consumes administration time, generates unnecessary alerts (alert fatigue), and can mask more serious problems. Resolving these jobs improves overall system reliability.
Recurring failing jobs
Jobs that fail consistently must be investigated immediately. They may indicate:
- Incorrect configuration
- Insufficient permissions
- Broken dependencies
- Connectivity issues
Success vs Failed Chart
Success vs Failed timeseries
Chart of successful vs failed execution evolution.
Importance: Temporal visualization allows identifying correlations between failures and specific events (deployments, configuration changes, load peaks). It also helps detect gradual system degradation before it becomes a critical problem.
Interpretation Guide
Investigation Priorities
| Situation | Priority | Action |
|---|---|---|
| Failed job | High | Review error logs immediately |
| Job exceeds duration | Medium | Investigate locks and performance |
| Cancelled job | Medium | Determine if intentional |
| Failure pattern | High | Root cause analysis |
Key Metrics
| KPI | Target | Interpretation |
|---|---|---|
| Success Rate | > 99% | Operations stability |
| Failed Executions | 0 | Each failure requires investigation |
| Duration Exceeded | 0 | No blocked jobs |
Views Used
| View | Description |
|---|---|
[JobStatus].[vwRunningJobs] | Currently running jobs |
[JobStatus].[vwRunningExceedDuration] | Jobs exceeding normal duration |
[JobStatus].[vwCountStatus] | Execution status count |
[JobStatus].vwJobsEnabled | Number of enabled jobs |
[JobStatus].[vwJobsDisabled] | Number of disabled jobs |
Best Practices for SQL Server Agent Jobs
Job Configuration
- Document the purpose: Each job should have a clear description of its function and owner
- Appropriate category: Assigning jobs to logical categories facilitates administration and filtering
- Execution account: Use service accounts with minimum necessary privileges (least privilege principle)
- Coherent scheduling: Avoid overlapping jobs that compete for the same resources
- Timeouts: Configure reasonable time limits to avoid jobs running indefinitely
Notification Configuration
- Notify on failures: Configure email or event alerts for critical job failures
- Defined operators: Keep operators updated with valid email addresses
- Escalate appropriately: Configure escalated notifications for repeated failures
- Avoid over-notification: Don't notify routine successes to prevent alert fatigue
Error Handling
- Failure action: Define whether the job should continue or stop when a step fails
- Automatic retries: Configure retries for transient errors (connectivity, locks)
- Retry interval: Space out retries to allow system recovery
- Detailed logging: Enable sufficient logging for diagnostics without impacting performance
Job Maintenance
- Periodic review: Audit jobs monthly to remove obsolete ones
- Credential updates: Rotate service account passwords according to policies
- Post-change testing: Validate jobs after SQL Server updates or schema changes
- Version control: Document changes to critical jobs with date and responsible party
Troubleshooting Strategies
Failed Job
- Review the error message in the job history (Job Activity Monitor or msdb.dbo.sysjobhistory)
- Verify permissions of the service account on all accessed resources
- Check connectivity if the job accesses external resources (linked servers, network files)
- Review disk space if it involves file writing or backups
- Verify dependent service status (SQL Server Agent, external services)
- Review recent changes on the server or affected database
Job with Exceeded Duration
- Verify active locks using sp_who2 or sys.dm_exec_requests
- Review the execution plan to identify costly operations
- Check statistics and index fragmentation on involved tables
- Analyze data growth since last normal execution
- Verify server resources (CPU, memory, I/O) during execution
- Compare with previous executions to identify behavior changes
Unexpectedly Cancelled Job
- Review who cancelled the job (SQL Server Error Log, Event Viewer)
- Verify if there was a restart of the SQL Server Agent service
- Check failover in high availability environments (Always On, Clustering)
- Review timeout policies configured in the job
- Verify maintenance scripts that might cancel jobs
Jobs with Recurring Failures
- Identify pattern: same error, same time, same day of week
- Correlate with events: backups, reindexing, usage peaks
- Review dependencies: stored procedures, linked servers, files
- Analyze historical logs to find when the problem started
- Test in controlled environment if the error can be reproduced
- Escalate to development if the problem is in the job code
Microsoft References
To delve deeper into SQL Server Agent administration and troubleshooting, consult the official documentation:
- SQL Server Agent - General service introduction
- Create a Job - Step-by-step guide to creating jobs
- Schedule a Job - Schedule configuration
- Configure Operators - Alert recipient management
- SQL Server Agent Alerts - Automatic alert configuration
- View Job History - Job activity monitoring
- Implement SQL Server Agent Security - Security best practices
- Troubleshoot Failed Jobs - Troubleshooting guide