I find the built-in MS SQL Studio tools for managing Agent jobs to be a little frustrating. What tools out there have you found helpful?

Offhand, there are three things I'd like to see in such a tool:

  • A graphical summary of which jobs ran when, for how long, and whether they succeeded.
  • A current status view, like the Job Activity Monitor, but refreshed in near-real time.
  • A more convenient interface for duplicating or modifying jobs (e.g., compare two job steps without being blocked by modal dialogs).

It would probably be simple to write a little app to handle this, but someone's surely already done it, and done it better.

This is obviously a subjective question, so if a mod of some sort wanders past, feel free to make it a CW.


Did some googling around and here are some software that may interest you:

  1. SQL Job Manager https://www.idera.com/productssolutions/freetools/sqljobmanager

  2. Query Currently Running SQL Server Agent Jobs http://sqlconcept.com/2011/06/25/how-to-query-currently-running-sql-server-agent-jobs/

If anyone has more links to add, go ahead!

For point 3, the link query and notes are here -

Today I went on a quest to discover a way to list all currently running SQL Server Agent jobs. As every other quest, this one also started with Google-ing. :)

Within 2 minutes I found this great post by Brent Ozar on SQLServerPedia. Why is this post so great? It is so great because Brent figured that if you query only the sysjobs and the sysjobhistory tables you will not get accurate current job status. I.e. in the sysjobhistory table you have a column run_status, however (even though according to the BOL the possible values for this column are “Status of the job execution: 0 = Failed,1 = Succeeded,2 = Retry,3 = Canceled,4 = In progress”) in reality, the value will never be 4 (In Progress). Actually, in the sysjobhistory table is kept historical data of each job step executed, which means that the status of the step is updated only after the next step is executed. In other words, the table is NOT updated in real time, nor every other second.

So, Brent figured out that there is a undocumented stored procedure sys.xp_sqlagent_enum_jobs, which is part of sp_help_job, which can give the current execution status of the Agent Job.

Even though I found a way to get the currently running jobs, I was not happy with this script because it runs only on SQL 2005 / 2008.

What should I do if I have a SQL 2000 instance and am very curious about the currently running jobs?

With a bit of Tim Chapman’s help (the master of www.SQLServerNation.com) I figured out how to do it. THANK YOU, Tim!

Here is the final script, which will run on SQL 2000, 2005 and 2008, and which will give you the currently running SQL Server agent jobs. (As you can see, the difference with Brent’s script is very little: instead of “sys.xp_sqlagent_enum_jobs” I am using “master.dbo.xp_sqlagent_enum_jobs” and I am limiting the resultset to get only the currently running jobs by using “where x.running = 1″).

Simple as it is. Enjoy.

FROM    tempdb.dbo.sysobjects
WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')
DROP TABLE [tempdb].[dbo].[Temp1]
CREATE TABLE [tempdb].[dbo].[Temp1]
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner   sysname
DECLARE @is_sysadmin   INT
SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner   = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]

--EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time    = right ('000000' + last_run_time, 6),
next_run_time    = right ('000000' + next_run_time, 6);
SELECT j.name AS JobName,
j.enabled AS Enabled,
CASE x.running
CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
WHEN x.last_run_date > 0
convert (datetime,
substring (x.last_run_date, 1, 4)
+ '-'
+ substring (x.last_run_date, 5, 2)
+ '-'
+ substring (x.last_run_date, 7, 2)
+ ' '
+ substring (x.last_run_time, 1, 2)
+ ':'
+ substring (x.last_run_time, 3, 2)
+ ':'
+ substring (x.last_run_time, 5, 2)
+ '.000',
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
AS LastRunOutcome,
WHEN h.run_duration > 0
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
AS LastRunDuration
FROM          [tempdb].[dbo].[Temp1] x
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
msdb.dbo.syscategories c
ON j.category_id = c.category_id
msdb.dbo.sysjobhistory h
ON     x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
where x.running = 1
  • I gave SQLjobvis a try; it does let me visualize the schedule somewhat, but it's not really any better than what I could do for myself in an hour. I'd have put the time axis vertically, so users could easily scroll through the days. – Jon of All Trades Mar 26 '12 at 21:01

I know this is an older post, and I have personally had the same problem. Managing multiple SQL agent jobs across multiple instances, so I decided to create SQL Agent Insight, found here > http://brentec.ca. It is an evolving product and will get out of beta in the near future and it is continually being updated and suggestions are welcome. This is a project I work on during my free time as I consult and also have a full-time gig, but if the suggestions are worth-while, they get put into the hopper for the future.

Currently it is just a monitoring tool for SQL agent, with some scripting ability. Currently sits at version 0.11 with 2-3 updates per year and have contact information for some assistance. Yes, right now, there isn't any online help, but since it is pretty much a read-only product, no damage can be done to the monitored instance of SQL.


Did you try reports feature?

Right click on SQL Agent => Reports => standard reports

  • I hadn't, but they're not terribly helpful. There are two, and they just show the number of executions and average run time. – Jon of All Trades Feb 29 '12 at 18:35
  • To be clear, I'm not ruling out SQL Studio reports, if someone has a useful report they'd like to share. – Jon of All Trades Mar 7 '12 at 17:28

Another one to add to the list:

One thing I like about this one is that it integrates with other components such as Windows Scheduler and Oracle tasks.

Thanks to Mark for bringing this up when I asked on The Heap.

Not the answer you're looking for? Browse other questions tagged or ask your own question.