Why SQL Agent Job Visibility Matters
You can query SQL Server Agent jobs and schedules directly from the msdb system database using a single T-SQL script. The script joins sysjobs, sysschedules, sysjobschedules, and sysjobhistory to return job names, enabled status, frequency, next run time, and maximum historical run duration in a single result set.
Most SQL Server environments accumulate Agent jobs over time. Backup jobs, index maintenance, integrity checks, ETL processes, reporting extracts, custom cleanup routines - they all pile up. Without a clear view of what's running and when, you're flying blind. Performance issues that seem random often have a perfectly logical explanation: two resource-intensive jobs scheduled to overlap, or a maintenance job that was quietly disabled months ago and never noticed.
This is one of the first queries we run when investigating unexplained performance degradation on a client's SQL Server. If something's slow between 2am and 4am, the Agent job schedule is one of the first places to look.
What Does the SQL Agent Job Schedule Query Return?
The script below queries the msdb database and returns the following columns for every scheduled Agent job:
- Job Name - the name of the SQL Agent job
- Job Enabled - whether the job is currently active (Yes/No)
- Frequency - how often the job runs (Once, Daily, Weekly, Monthly, etc.)
- Start Date - the date the schedule becomes active, formatted as YYYY/MM/DD
- Start Time - the scheduled start time, formatted as HH:MM:SS
- Max Duration - the maximum run duration recorded in job history, formatted as HH:MM:SS
- Subday Frequency - for jobs that run multiple times per day, how often within the day (e.g. "Every 15 Minutes")
The query uses a UNION to handle two scenarios: jobs where the next run time is 0 (typically disabled or completed one-time jobs), and jobs with a scheduled future run time. This ensures you see the complete picture rather than only currently active schedules.
The T-SQL Script
Run this against your SQL Server instance. It must be executed in the context of the msdb database.
USE msdb
GO
SELECT dbo.sysjobs.Name AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Start Date' = CASE active_start_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),active_start_date),1,4) + '/' +
substring(convert(varchar(15),active_start_date),5,2) + '/' +
substring(convert(varchar(15),active_start_date),7,2)
END,
'Start Time' = CASE len(active_start_time)
WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))
WHEN 3 THEN cast('00:0'
+ Left(right(active_start_time,3),1)
+':' + right(active_start_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(active_start_time,5),1)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
END,
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(run_duration,3),1)
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every '
+ right(dbo.sysschedules.freq_subday_interval,2)
+ ' '
+ CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules
ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (
SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id
) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0
UNION
SELECT dbo.sysjobs.Name AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Start Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Start Time' = CASE len(next_run_time)
WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
END,
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(run_duration,3),1)
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every '
+ right(dbo.sysschedules.freq_subday_interval,2)
+ ' '
+ CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules
ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (
SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id
) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0
ORDER BY 'Start Date', 'Start Time'How to Interpret the Results
Once you run the query, sort the output by Start Date and Start Time (the ORDER BY at the end of the second SELECT handles this automatically). Look for a few specific patterns.
Overlapping jobs. If two jobs with significant Max Duration values are scheduled close together or at the same time, they may be competing for I/O, CPU, or memory. Index rebuild jobs are a common offender - a rebuild on a large table can run for 30 to 90 minutes on a busy OLTP system and will hammer disk throughput the entire time.
Disabled jobs that shouldn't be. The "Job Enabled = No" rows are easy to overlook, but they're worth reviewing. Backup jobs that were disabled during a migration and never re-enabled are a genuine risk we've encountered more than once.
Jobs with no history. A NULL in the Max Duration column means the job has never completed successfully, or the history has been purged. Either scenario warrants investigation. SQL Server Agent history is capped by default at 1,000 rows per job and 10,000 rows total, which means history can disappear faster than you'd expect on a busy server.
Unexpectedly long run times. If a job that used to take 5 minutes is now showing a Max Duration of 45 minutes, that's a signal. Index fragmentation, data growth, or a plan regression could all be contributing factors.
What the UNION Is Doing
The query is split into two SELECT statements joined by a UNION. The first half retrieves jobs where Next_run_time = 0, which covers jobs that have no upcoming scheduled execution. This includes disabled jobs, jobs with expired schedules, and one-time jobs that have already run. The second half retrieves jobs with an active next run time and uses next_run_date and next_run_time rather than active_start_date and active_start_time for the schedule columns.
Using UNION rather than UNION ALL means duplicate rows are eliminated, though in practice you're unlikely to see duplicates with this particular query structure.
Permissions Required
To execute this query you need at minimum SELECT permission on the msdb database tables, or membership in the SQLAgentReaderRole database role in msdb. Members of the sysadmin fixed server role can run it without additional grants. For environments where you're providing read-only access to support staff or monitoring tools, SQLAgentReaderRole is the appropriate and least-privileged option.
Key Takeaways
- SQL Agent job schedules and run history are stored in msdb and are fully queryable via T-SQL without needing GUI access to SQL Server Management Studio.
- The Max Duration column is derived from the historical maximum run time across all recorded executions, making it a useful baseline for identifying jobs that are running longer than expected.
- Overlapping high-duration jobs are a common and frequently overlooked cause of periodic performance degradation, particularly during overnight maintenance windows.
- Disabled jobs appear in the result set and should be reviewed regularly - a disabled backup or integrity check job is a silent risk.
- SQL Server Agent history retention is limited by default settings, so Max Duration will show NULL for jobs with no retained history. Review your msdb history cleanup settings if this is a concern.
Auditing SQL Agent jobs and schedules is a standard part of the SQL Server health checks DBA Services performs for new and existing clients. If you're managing a SQL Server environment and aren't certain what's running, when, and for how long, that's a gap worth closing. Get in touch with the DBA Services team to discuss a health check or ongoing managed support.
Need help with your SQL Servers?
Find out what's really going on inside your SQL Server environment.
Our health checks uncover critical misconfigurations in 97% of reviews.