SQl job find running state with open query

sp_configure 'show advanced options', 1;

RECONFIGURE;




Go



sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;  


Declare @SQLServerJobID varchar(100)

select @SQLServerJobID=job_id from msdb..sysjobs where name ='Job name'

Declare @DynSQL Nvarchar(MAX)

Declare @current_execution_status int

SET @DynSQL =

'SELECT




@current_execution_status=current_execution_status

FROM OPENROWSET


(''SQLOLEDB'', ''Server=.;TRUSTED_CONNECTION=YES;'',''SET FMTONLY OFF EXEC msdb..sp_get_composite_job_info @job_id=''''' + CONVERT(NVARCHAR(50),@SQLServerJobID) + ''''''') '

EXEC sp_executesql @DynSQL, N'@current_execution_status INT OUTPUT',@current_execution_status output

Select @current_execution_status

 

check sql job is running or not

SELECT





case h.run_status

when 0 then 'Failed '

when 1 then 'Succeeded '

when 2 then 'Retry (step only)'

when 3 then 'Canceled'

when 4 then 'In-progress message '

when 5 then 'Unknown'

end as [run status ]





FROM

msdb.dbo.sysjobhistory h

INNER JOIN

msdb.dbo.sysjobs_view j

on h.job_id = j.job_id

where

j.name = 'job name'

AND run_status in (0,1,2,3,4) -- Failed, cancelled, in prog, unknown


group by run_status