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 |
(15 votes, average: 4.67 out of 5)
Loading ...
Thanks for this sample …