May 29, 2013 at 9:22 am
The query below I searched from net, this returns jobs status. When I give job Id in filter of this query this will give job status of "Success" but actually my job is currently in executing stage. I want to get all jobs that are currently in executing status. Would you please help to modify this query or suggest me another one.
Thanks
Use msdb
go
select distinct j.Name as "Job Name", --j.job_id,
case j.enabled
when 1 then 'Enable'
when 0 then 'Disable'
end as "Job Status", jh.run_date as [Last_Run_Date(YY-MM-DD)] ,
case jh.run_status
when 0 then 'Failed'
when 1 then 'Successful'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as Job_Execution_Status
from sysJobHistory jh, sysJobs j
where j.job_id = jh.job_id and jh.run_date =
(select max(hi.run_date) from sysJobHistory hi where jh.job_id = hi.job_id )
May 29, 2013 at 9:41 am
Please try this (you can alter the returned columns as needed).
USE [msdb]
GO
SELECT *
INTO #temp
FROM OPENROWSET('SQLNCLI', 'server=SERVERNAME;trusted_connection=yes',
'set fmtonly off exec msdb.dbo.sp_help_job')
SELECT name, enabled, current_execution_step, current_execution_status
FROM #Temp
DROP TABLE #Temp
March 13, 2014 at 5:00 am
follow the link, you will get the needed.
March 13, 2014 at 5:25 am
Check the system table sysjobactivity and find out if you can get what you want based on start & Stop execution date?
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 13, 2015 at 3:05 pm
Simple script to find SQL Server agent job running at this moment.
exec msdb.dbo.sp_help_job @execution_status=1
http://www.sqlserverbox.com/sql-server-agent-job-running-at-this-instant.html
http://sqlism.blogspot.com/2015/04/sql-server-agent-job-running-at-this.html
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply