I use the following to identify the currently running job for a spefic job in a script.
First do a Select * from msdb.dbo.sysjobs
Get your job_id and then insert it into my script.
Also you can query the temp table for whatever you want. In this example I query it for a specfic job and the job is currently running.
[sql]
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job int
insert into #xp_results
exec master..xp_sqlagent_enum_jobs 1,''
set @job = (select count(*) from #xp_results where job_id = '46521945-519A-45F2-BB7F-02034DB4CA1C' and job_state = '1')
drop table #xp_results
if @job = 1
begin
RAISERROR ('The Master pkh 1-8 64 is hung ', -- Message text.
16, -- Severity.
1 -- State.
);
end
[/sql] |