How to find that job is running?

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/howtofindthatjobisrunning.asp

  • Clinton Herring

    Ten Centuries

    Points: 1160

    Leo, I used a similar approach some years back but then found a better way...it makes use of xp_sqlagent_enum_jobs which gives the actual running status of a job (among other things).

    if (select object_id('tempdb.dbo.#jobstauts')) > 0

    drop table dbo.#jobstauts

    create table #jobstauts(

    JobID UNIQUEIDENTIFIER null,

    LastRunDate int null,

    LastRunTime int null,

    NextRunDate int null,

    NextRunTime int null,

    NextRunScheduleID int null,

    RequestedToRun int null,

    RequestSource int null,

    RequestSourceID sysname null,

    Running int null,

    CurrentStep int null,

    CurrentRetryAttempt int null,

    State int null

    )

    declare @jobid UNIQUEIDENTIFIER

    set @jobid = convert(UNIQUEIDENTIFIER,(select job_id

    from msdb.dbo.sysjobs

    where name = ''))

    insert into dbo.#jobstauts

    exec master.dbo.xp_sqlagent_enum_jobs 1, 'sa', @jobid

    if (select Running from dbo.#jobstauts) = 1 -- running

    Begin

    (additional code>

    End

  • LP-181697

    SSC Eights!

    Points: 966

    Thanks, but I clearly indicated the usage of the extended stored procedure at the beginning of the article:

    In addition, there is an article written by Gregory A. Larsen. http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2  His article shows how to find that any job is running.  But to give the answer "Is job running?" for the particular job the article is using undocumented stored procedure xp_sqlagent_enum_jobs. Microsoft SQL Server has always had nifty undocumented functions that people are tempting to use. However, Microsoft has always recommended not using those functions because they can change in future releases. Using undocumented features rarely burning DBA. But with SQL Server 2005 it may be the issue. 

    Please read the article carefully. This proc is undocumented. I am trying to show the way to stay within the documented boundary.

    Leo P.

  • EugeneZ

    SSC Veteran

    Points: 237

    just tested xp_sqlagent_enum_jobs  on sql server 2005:

    working fine.

    How many DBA do you know who do not use "undocumented" sp,  DBCC, etc?

     

  • noeld

    SSC Guru

    Points: 96590

    The article is good but I think is overcomplicating the solution. Greg Larsen's Article besides using an undocummented feature is also missing a simple posibility:

    select *

    from openrowset('SQLOLEDB',

                    'Trusted_Connection=yes; Initial Catalog=Local',

                    'SET FMTONLY OFF; exec msdb.dbo.sp_help_job ')

    -- Where ...

    And you get your problem solved

    - without any undocumented feature

    - with a very simple query

    - without artificilly altering your jobs

    Hope this helps

     


    * Noel

  • EugeneZ

    SSC Veteran

    Points: 237

    noeld:

    Good one.

    Thank you for "undocumented" way to get  jobs info

     

  • noeld

    SSC Guru

    Points: 96590

    EugeneZ ,

    All the functions used in that query are documented!!

    Not all possible queries can be in BOL

    Cheers,

     

     


    * Noel

  • Jeff Hachmann

    SSC-Addicted

    Points: 438

    You could also use the following I got from an earlier article on this site.

    msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

     

    Jeff

  • Clinton Herring

    Ten Centuries

    Points: 1160

    Yes, you clearly stated that but what's your beef with "undocumented"? This extended proc is documented somewhere (inside Microsoft)...it's just not available to us, though I'm sure some one could get their hands on it if they persisted and had the right contacts. And fear of change!? In SQL Server land??!! All the !documented! stuff has changed from version to version and sometimes between service packs. I had to change the code below (which I no longer use) because of changes bewteen SQL 7.0 and SQL 2k. (BTW MS also recommends that we don't read system tables directly either.)

    Use msdb

    go

    if exists (select *

    from dbo.sysobjects

    where id = object_id(N'[dbo].[usp_JobStatus]')

    and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    Drop procedure [dbo].[usp_JobStatus]

    Go

    Create procedure [dbo].[usp_JobStatus]

    @jobname sysname = null,

    @datetime datetime = null,

    @status int = -1 Output

    as

    /*******************************************************************/

    --Name : dbo.usp_JobStatus

    --Server : Generic

    --Database : msdb

    --

    --Description : Returns the run_status of the most recent step 0

    -- of a job passed by name, otherwise returns -1

    -- : Works in ISQL/W, ISQL, OSQL & Query Analyzer

    --Date : 04/25/2003

    --Author : Clint Herring

    --

    --History : Date Initials Modifications

    -- 07/15/2004 CH Updated to take into account

    -- differences between SQL 7.0 and SQL 2k

    /*******************************************************************/

    /*

    Run_Status

    0 = Failed

    1 = Succeeded

    2 = Retry

    3 = Canceled

    4 = In progress

    5 = Hasn't run since midnight of parameter date

    6 = Job doesn't exist

    */

    Set NOCOUNT On

    declare @date int

    declare @time int

    declare @jobid varchar(36)

    declare @stepid int

    select @jobid = job_id from msdb.dbo.sysjobs where name = @jobname

    If @jobid is null return 6

    select @date = convert(int, convert(varchar(8), @datetime, 112))

    select @time = datepart(hour, @datetime)*10000 +

    datepart(minute, @datetime)*100 +

    datepart(second, @datetime)

    select @stepid = step_id

    from msdb.dbo.sysjobhistory

    where job_id = @jobid

    and instance_id =

    (select max(instance_id) from msdb.dbo.sysjobhistory where job_id = @jobid)

    If (@stepid = 0 or @stepid is null)

    Begin

    select @status =

    Isnull((select b.run_status

    from msdb.dbo.sysjobs a

    join msdb.dbo.sysjobhistory b

    on a.job_id = b.job_id

    where name = @jobname

    and step_id = 0

    and instance_id =

    (select max(instance_id)

    from msdb.dbo.sysjobhistory c

    where c.step_id = 0

    and c.job_id = a.job_id

    and c.run_date >= @date

    and c.run_time + c.run_duration +

    case when (c.run_time + c.run_duration)%100 >60 then 40 else 0 end +

    case when (c.run_time + c.run_duration)%10000 >6000 then 4000 else 0 end

    >= @time)),5)

    End

    Else

    Begin

    Set @status = 4

    End

    go

    /* Example usage

    use msdb

    If (select object_id('tempdb.dbo.#tmpstatus')) > 0

    drop table #tmpstatus

    Create table #tmpstatus (name sysname null, status int null)

    declare @status int

    declare @date datetime

    declare @name sysname

    set @date = '07/16/2004' --date part prior to last run

    select @name = min(name) from sysjobs

    Print 'The following jobs statuses are as of ' + convert(varchar(26),@date,113)

    while @name is not null

    begin

    exec msdb.dbo.usp_JobStatus @name, @date, @status output

    insert into #tmpstatus values (@name, @status)

    select @name = min(name) from sysjobs where name > @name

    end

    select convert(varchar(40),name) [job name],

    case when status = 0 then 'Failed'

    when status = 1 then 'Succeeded'

    when status = 2 then 'Retry'

    when status = 3 then 'Canceled'

    when status = 4 then 'In progress'

    else 'has not run since ' + convert(varchar(26),@date,113)

    end [status]

    from #tmpstatus

    */

  • Clinton Herring

    Ten Centuries

    Points: 1160

    I think master.dbo.xp_sqlagent_enum_jobs reads from memory (pretty sure) and appears to give you what Enterprise Manager (EM) returns whereas msdb.dbo.sp_get_composite_job_info and msdb.dbo.sp_help_job get their information from the systemtables. The difference appears to be that the current status of a job is written to the tables after the fact, for instance, viewing job history in EM tells the last step that completed (or failed) and one assumes that the next step is running. The extended proc tells what step is currently running.

  • noeld

    SSC Guru

    Points: 96590

    That is not correct!

    sp_help is a wrapper that calls sp_get_composite_job_info which is a wrapper to call xp_sqlagent_enum_jobs

    Cheers!!!

     


    * Noel

  • Amr Salah

    SSC Enthusiast

    Points: 150

    you may get lots of information about the job using sp_help_job

    heres link in BOL (2005) , also works with 2000

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/8a8b6104-e0e4-4d07-a2c3-f4243ee0d6fa.htm

    the result set has column current_execution_status , gives you the current status of the job

  • utsav

    SSC Veteran

    Points: 234

    I Found an issue in this approach, if I start this job from step#2 it doesnt return correct result.

    For your reference, following is the query I used (hope not missing any thing obvious)

    IF exists ( select * from msdb..sysjobhistory sjh

    inner join msdb..sysjobs sj on sj.job_id = sjh.job_id

    where sj.name = @jobname

    and step_name = '(Job outcome)' )

    BEGIN

    select @isRunning=COUNT(*) from msdb..sysjobhistory sjh

    inner join msdb..sysjobs sj

    on sj.job_id = sjh.job_id

    where sj.name = @jobname

    and sjh.instance_id > (select max(instance_id)

    from msdb..sysjobhistory sjh1

    where sjh1.step_name = '(Job outcome)' and sjh1.job_id = sj.job_id )

    END

    ELSE

    select @isRunning=count(*) from dbo.sysjobhistory sjh

    inner join sysjobs sj on sj.job_id = sjh.job_id

    where sj.name = @jobname

    --print @isRunning

    if (@isRunning>0)

    SELECT 1

    ELSE

    SELECT 0

  • dbaforever

    Hall of Fame

    Points: 3738

    And this still works ... for 2005.

    Thanks very much, noeld!

  • derek.colley

    SSCrazy Eights

    Points: 8040

    I agree with Leo, the sysjobs and sysjobhistory tables can be quite useful. Here's a query I wrote which will return the average, max, min, range and standard deviation of step runs in milliseconds over the course of given date range for a given job. Very useful when determining which parts of a job are taking a long time.

    SELECT AVG(sjh.run_duration) AS avg_run_duration_ms,

    MAX(sjh.run_duration) AS max_run_duration_ms,

    MIN(sjh.run_duration) AS min_run_duration_ms,

    MAX(sjh.run_duration) - MIN(run_duration) AS range_run_duration_ms,

    ROUND(STDEV(sjh.run_duration),2) AS stdev_run_duration_ms,

    sjh.step_id, sjh.step_name

    FROM sysjobhistory sjh

    INNER JOIN sysjobs sj ON sjh.job_id = sj.job_id

    INNER JOIN SANDBOX.dbo.Numbers n ON sjh.step_id = n.Number

    WHERE sj.name = 'xxx' -- put job name here

    AND sjh.run_date BETWEEN 20120616 AND 20120716 -- put date range here

    AND step_id <> 0

    GROUP BY sjh.step_id, sjh.step_name

    ORDER BY step_id

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply