February 6, 2012 at 9:11 pm
Comments posted to this topic are about the item TSQL function to detect SQL Agent job state
February 6, 2012 at 11:01 pm
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
February 7, 2012 at 1:43 am
people may also want to look at sp_help_job and xp_sqlagent_enum_jobs
---------------------------------------------------------------------
February 7, 2012 at 2:07 am
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.
February 7, 2012 at 2:32 am
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
February 7, 2012 at 12:56 pm
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 --
February 8, 2012 at 2:52 am
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?
March 19, 2012 at 11:39 am
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-2 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-2 = CAST(SUBSTRING(@hexdigits, @i % 16 + 1, 1) AS CHAR(1))
-- shift over one character
SET @i = @i / 16
-- get the second character
SET @s-2 = CAST(SUBSTRING(@hexdigits, @i % 16 + 1, 1) AS CHAR(1))
+ @s-2
-- build string of hex characters
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
September 20, 2012 at 12:57 am
its not working if ur job is wrap on dtsx
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply