Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

4 Answers

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

  1. SQL Job Vis (this seems the most popular, according to some forum and blog posts) http://www.sqlsoft.co.uk/sqljobvis.php

  2. SQL Job Manager http://www.idera.com/Free-Tools/SQL-job-manager/

  3. 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.

IF EXISTS (SELECT *
FROM    tempdb.dbo.sysobjects
WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')
)
DROP TABLE [tempdb].[dbo].[Temp1]
GO
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
WHEN 1
THEN
'Running'
ELSE
CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date > 0
THEN
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',
121
)
ELSE
NULL
END
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'
END
AS LastRunOutcome,
CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS LastRunDuration
FROM          [tempdb].[dbo].[Temp1] x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
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
share|improve this answer
    
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
    
The link for idera SQL job manager is actually at - idera.com/productssolutions/freetools/sqljobmanager –  Steam Feb 6 at 19:28
    
Moderators, please approve my edit to this post. Thanks. –  Steam Feb 6 at 19:33
add comment

Did you try reports feature?

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

share|improve this answer
    
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
add comment

I'm not sure this satisfies all your criteria, but you might look at this:

http://www.sqlsoft.co.uk/sqljobvis.php

share|improve this answer
    
The web site makes me a little leery to run their .exe. Some contact information would be a start. –  Jon of All Trades Mar 21 '12 at 22:22
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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