TSQL function to detect SQL Agent job state

  • bret.lowery

    Old Hand

    Points: 302

    Comments posted to this topic are about the item TSQL function to detect SQL Agent job state

  • shell_l_d

    Ten Centuries

    Points: 1385

    This may help too, per http://www.sqlservercentral.com/Forums/FindPost829648.aspx 🙂

    -- link sysprocesses to sysjobs and sysjobsteps

    select

    p.spid,

    p.program_name,

    j.job_id,

    job_name = coalesce(j.[name], ''),

    jobstep_id = coalesce(js.[step_id], ''),

    jobstep_name = coalesce(js.[step_name], '')

    from master.dbo.sysprocesses p

    --left outer join msdb.dbo.sysjobs j on substring(p.program_name,32,32) = substring(master.dbo.fn_varbintohexstr(j.job_id),3,100)

    left outer join msdb.dbo.sysjobs j on master.dbo.fn_varbintohexstr(j.job_id) = substring(p.program_name,30,34)

    left outer join msdb.dbo.sysjobsteps js on j.job_id = js.job_id and js.step_id = substring( p.program_name, 72, len(p.program_name)-72 )

    where

    p.spid >= 50

    and p.status <> 'sleeping'

    and p.spid <> @@spid

    OR http://www.sqlservercentral.com/Forums/FindPost837738.aspx:

    -- NOTE: dts_name will ONLY return a value 'IF' DTSRUN used with dtsname.

    SELECT

    p.SPID,

    Blocked_By = p.Blocked,

    p.Status,

    p.LogiName,

    p.HostName,

    p.open_tran,

    Program = coalesce('Job: ' + j.[name], p.program_name),

    p.program_name,

    job_name = coalesce(j.[name], ''),

    jobstep_id = coalesce(js.[step_id], ''),

    jobstep_name = coalesce(js.[step_name], ''),

    js.[command],

    dts_name = coalesce(d.[name], ''),

    DBName = db_name(p.dbid),

    Command = p.cmd,

    CPUTime = p.cpu,

    DiskIO = p.physical_io,

    LastBatch = p.Last_Batch,

    -- LastQuery = coalesce( (select [text] from sys.dm_exec_sql_text(p.sql_handle)), '' ), -- SQL Server 2005+

    -- LastQuery = coalesce( (select * from ::fn_get_sql(p.sql_handle)), '' ), -- SQL Server 2000 ? FAILS

    p.WaitTime,

    p.LastWaitType,

    LoginTime = p.Login_Time,

    RunDate = GetDate(),

    [Server] = serverproperty('machinename'),

    [Duration] = datediff(second, p.last_batch, getdate())

    FROM master.dbo.sysprocesses p

    left outer join msdb.dbo.sysjobs j on master.dbo.fn_varbintohexstr(j.job_id) = substring(p.program_name,30,34)

    left outer join msdb.dbo.sysjobsteps js on j.job_id = js.job_id and js.step_id = SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 )

    left outer join msdb.dbo.sysdtspackages d on js.command like ('%dtsrun%'+cast(d.[name] as varchar(100))+'%')

    where

    p.spid > 50

    -- and p.status <> 'sleeping'

    and p.spid <> @@spid

    order by

    p.spid

  • george sibbald

    SSC Guru

    Points: 104210

    people may also want to look at sp_help_job and xp_sqlagent_enum_jobs

    ---------------------------------------------------------------------

  • berzat.museski

    SSC Journeyman

    Points: 99

    george sibbald (2/7/2012)


    people may also want to look at sp_help_job and xp_sqlagent_enum_jobs

    Yes, especially when you are out of permissions to directly query sys tables.

  • Ness

    SSCarpal Tunnel

    Points: 4282

    Already mentioned SP_HELP_JOB with the parameters but also worth looking at :

    SP_HELP_JOBACTIVITY

    SP_HELP_JOBHISTORY

    🙂

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4454

    Grasshopper, both your posted SQL statements result in invalid length passed to substring errors when I attempted to execute the statements on SQL Server 2008R2 RTM.

    - -

    HTH -- Mark D Powell --

  • rhydian

    Valued Member

    Points: 57

    wouldn't it be easier just to set the value of a Field when the 1st job is running...then your 2nd job could just read the value and run/not run depending on what it is?

  • Dmitriy Burtsev

    Ten Centuries

    Points: 1188

    I like your idea but I make a few changes to meet my environment.

    CREATE VIEW dbo.v_jobs

    AS

    SELECT j.name AS JobName,

    j.job_id,

    CAST (j.job_id AS NCHAR (36)) AS job_id_as_text,

    CAST (j.job_id AS BINARY (16)) AS job_id_as_bin

    FROM msdb.dbo.sysjobs j

    GO

    CREATE FUNCTION dbo.uf_HexToChar

    (

    @binaryValue VARBINARY(100) ,

    @numBytes INT

    )

    RETURNS VARCHAR(200) WITH SCHEMABINDING

    AS

    /*

    we need a hex-to-GUID converter to match job name from sys.dm_exec_sessions to a job_id value.

    use dbo.uf_HexToChar (j.job_id, 16)

    */

    BEGIN

    DECLARE @i VARBINARY(10) ,

    @hexdigits CHAR(16) ,

    @S VARCHAR(100) ,

    @h VARCHAR(100) ,

    @currentByte SMALLINT

    SET @hexdigits = '0123456789ABCDEF'

    SET @currentByte = 0

    SET @h = ''

    -- process all bytes

    WHILE @currentByte < @numBytes

    BEGIN

    SET @currentByte = @currentByte + 1

    -- get first character of byte

    SET @i = SUBSTRING(CAST(@binaryValue AS VARBINARY(100)), @currentByte, 1)

    -- get the first character

    SET @S = CAST(SUBSTRING(@hexdigits, @i % 16 + 1, 1) AS CHAR(1))

    -- shift over one character

    SET @i = @i / 16

    -- get the second character

    SET @S = CAST(SUBSTRING(@hexdigits, @i % 16 + 1, 1) AS CHAR(1))

    + @S

    -- build string of hex characters

    SET @h = @h + @S

    END

    RETURN(@h)

    END

    GO

    CREATE FUNCTION [dbo].[uf_IsSQLAgentJobRunning] (

    @SQLAgentJobNameOrId VARCHAR (128))

    RETURNS BIT

    AS

    BEGIN

    /* use

    dbo.uf_IsSQLAgentJobRunning ('Job name');

    dbo.uf_IsSQLAgentJobRunning (0x91527ABA8FC12F4CA896687736BFAB23);

    dbo.uf_IsSQLAgentJobRunning ('BA7A5291-C18F-4C2F-A896-687736BFAB23');

    */

    DECLARE

    @rtn BIT,

    @JobName NVARCHAR (128),

    @JobHexString NCHAR (34);

    -- assume job is not running; must prove otherwise!

    SET @rtn = 0;

    -- scrub inputs

    SET @SQLAgentJobNameOrId = LTRIM (RTRIM (@SQLAgentJobNameOrId));

    IF (LEN (@SQLAgentJobNameOrId) = 0)

    BEGIN

    SET @SQLAgentJobNameOrId = NULL;

    END

    -- return NULL if NULL or blanks passed (sanity check)

    IF @SQLAgentJobNameOrId IS NULL

    RETURN NULL;

    -- trying to get job name

    WITH GetJobInfo (JobName, JobHexString)

    AS (SELECT JobName,

    master.dbo.uf_HexToChar (job_id, 16) AS JobHexString

    FROM master.dbo.v_jobs

    WHERE JobName = @SQLAgentJobNameOrId

    UNION

    SELECT JobName,

    master.dbo.uf_HexToChar (job_id, 16) AS JobHexString

    FROM master.dbo.v_jobs

    WHERE job_id_as_Text = @SQLAgentJobNameOrId

    UNION

    SELECT JobName,

    master.dbo.uf_HexToChar (job_id, 16) AS JobHexString

    FROM master.dbo.v_jobs

    WHERE job_id_as_bin = @SQLAgentJobNameOrId)

    SELECT @JobName = ji.JobName,

    @JobHexString = ji.JobHexString

    FROM GetJobInfo ji

    -- if no matching job found by job_id or name, return NULL

    IF @JobName IS NULL

    RETURN NULL

    -- if both msdb and dm_exec_sessions considers the job as "running", return 1

    IF EXISTS

    (SELECT 1

    FROM sys.dm_exec_sessions sp

    WHERE sp.Status IN ('running', 'sleeping')

    AND SUBSTRING (sp.[program_name], 32, 32) =

    @JobHexString)

    AND EXISTS

    (SELECT 1

    FROM msdb..sysjobs j

    WHERE j.name = @JobName

    AND EXISTS

    (SELECT 1

    FROM msdb..sysjobsteps sj

    INNER JOIN

    msdb..sysjobactivity ja

    ON ja.job_id = j.job_id

    WHERE sj.job_id = j.job_id

    AND ja.start_execution_date <=

    GETDATE ()

    AND ja.stop_execution_date IS NULL

    AND ja.session_id =

    (SELECT MAX (ja2.session_id)

    FROM msdb..sysjobactivity ja2

    WHERE ja2.job_id = j.job_id)))

    SET @rtn = 1

    RETURN @rtn

    END

    GO

  • ice-420339

    SSC Rookie

    Points: 37

    its not working if ur job is wrap on dtsx

Viewing 9 posts - 1 through 9 (of 9 total)

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