TSQL function to detect SQL Agent job state

Question

Post reply

TSQL function to detect SQL Agent job state

bret.lowery

Old Hand

Points: 302
More actions
February 6, 2012 at 9:11 pm

#251035

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

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

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

shell_l_d Ten Centuries Points: 1387 More actions · Answer 1

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 More actions · Answer 2

people may also want to look at sp_help_job and xp_sqlagent_enum_jobs

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

berzat.museski SSC Journeyman Points: 99 More actions · Answer 3

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 More actions · Answer 4

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: 4775 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

ice-420339

SSC Rookie

Points: 37

September 20, 2012 at 12:57 am

#1539909

its not working if ur job is wrap on dtsx

TSQL function to detect SQL Agent job state

Cookies on SQLServerCentral