Hello NG,

I need to start SQL Server Agent jobs frequently from my stored procedures
by using the code fragment:

   EXECUTE msdb..sp_start_job 'MyJob'

If the job is still running though, I get flodded with (completely harmless
but disturbing) error messages that tell me, that the job is running already.
Does anyone know how to find out if a job is currently running (and evaluate
the result: sp_help_job is not very usefull, as it returns three different
resultsets at a time which can hardly be evaluated by T-SQL code)?

Thanks in advance.
Christian

Try sp_help_job. The documentation doesn't say, but I guess that the @execution_status parm is an
output parm.

Show quoteHide quote

Try sp_help_job. The documentation doesn't say, but I guess that the
@execution_status parm is an output parm.

------------------------------
I tested it: it isn't. :(  Thank you for the trial!

I tested it: it isn't. :(
Thanks for the try!

Hi,


Select run_status from sysjobhistory sjh
inner join sysjobs sj
on sj.job_id = sjh.job_id
Where sj.name = 'Something'


HTH, Jens Suessmeyer.

Just to pick up the status:

Look in the BOl for further details on sysjobhistory:

run_status int Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress


HTH, jens SUessmeyer.

Hi Jens,
   this doesn't work. It shows the last outcome, not the current condition ...

I just figured this out a few weeks ago. Here is a sample of a step in a job
I run:

--This step makes sure the log shipping job is not running before doing the
backup and disables the job for the duration of the backup/restore job
DECLARE @is_sysadmin INT
DECLARE @job_owner   sysname
declare @job_name    sysname
declare @mailmsg varchar(1000)
declare @isstillrunning int
set nocount on
set @isstillrunning = 1
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)


while  @isstillrunning = 1
  begin
        set @is_sysadmin=1
        insert into  #xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs
@is_sysadmin, @job_owner
        if exists (select job_id,job_state from #xp_results
              where (job_id = '8500F05F-26B7-4C27-A7B7-45DF77A4E4EA' and
job_state = 1))
        begin
              truncate table #xp_results
              waitfor delay '00:02:00'
        end

       else
          begin
             set @isstillrunning = 0
             EXEC msdb..sp_update_job @job_id =
'8500F05F-26B7-4C27-A7B7-45DF77A4E4EA',
                 @enabled = 0
             drop table #xp_results
             return
          end

end
GO

I found this somewhere, in some posting I think, just can't remember where.
I did not write the bulk of it someone else does, but it works.

Jackie


Show quoteHide quote

Whats Wrong with the Query?
TSQL Debugger Oddity
Newbie:UNION with SELECT TOP
store image
DB design question.