Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

How to find that job is running? Expand / Collapse
Author
Message
Posted Tuesday, October 18, 2005 5:27 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:47 AM
Points: 6,259, Visits: 2,029

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
Post #229944
Posted Wednesday, October 18, 2006 4:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:17 AM
Points: 12, Visits: 62
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



Post #316154
Posted Friday, September 18, 2009 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 19, 2014 11:14 AM
Points: 2, Visits: 133
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
Post #790432
Posted Friday, October 22, 2010 12:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:41 PM
Points: 150, Visits: 499
And this still works ... for 2005.

Thanks very much, noeld!
Post #1009439
Posted Monday, July 16, 2012 3:21 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
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??!

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Post #1330000
Posted Tuesday, October 23, 2012 3:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 8:32 AM
Points: 31, Visits: 247
For your viewing pleasures... here is another working sample tested on sql 8 - 10.50

 IF EXISTS ( SELECT  * FROM    tempdb..sysobjects WHERE id = OBJECT_ID(N'#tmp_enum_jobs') ) 
DROP TABLE #tmp_enum_jobs;
ELSE
CREATE TABLE #tmp_enum_jobs (
job_id UNIQUEIDENTIFIER NOT NULL
, last_run_date NVARCHAR(20) NOT NULL
, last_run_time NVARCHAR(20) NOT NULL
, next_run_date NVARCHAR(20) NOT NULL
, next_run_time NVARCHAR(20) NOT NULL
, next_run_schedule_id INT NOT NULL
, requested_to_run INT NOT NULL
, request_source INT NOT NULL
, request_source_id NVARCHAR(256) NULL
, running INT NOT NULL
, current_step INT NOT NULL
, current_retry_attempt INT NOT NULL
, job_state INT NOT NULL );

DECLARE @job_owner NVARCHAR(256)
DECLARE @is_sysadmin INT

SET @is_sysadmin = ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0)
SET @job_owner = SUSER_SNAME()

INSERT INTO #tmp_enum_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

UPDATE #tmp_enum_jobs
SET last_run_time = RIGHT('000000' + last_run_time, 6)
, next_run_time = RIGHT('000000' + next_run_time, 6);

SELECT @@SERVERNAME AS Server_Name
, j.name AS Job_Name
, j.enabled AS Enabled
, CASE t.running
WHEN 1 THEN 'Running'
ELSE CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END AS Current_Status
, COALESCE(t.current_step, 0) AS Current_Step
, CASE WHEN t.last_run_date > 0
THEN CONVERT (DATETIME, SUBSTRING(t.last_run_date, 1, 4) + '-'
+ SUBSTRING(t.last_run_date, 5, 2) + '-'
+ SUBSTRING(t.last_run_date, 7, 2) + ' '
+ SUBSTRING(t.last_run_time, 1, 2) + ':'
+ SUBSTRING(t.last_run_time, 3, 2) + ':'
+ SUBSTRING(t.last_run_time, 5, 2) + '.000', 121)
ELSE NULL
END AS Last_Run_Time
, CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END AS Last_Run_Outcome
/*, CASE WHEN h.run_duration > 0
THEN ( h.run_duration / 1000000 ) * ( 3600 * 24 )
+ ( h.run_duration / 10000 % 100 ) * 3600 + ( h.run_duration
/ 100 % 100 )
* 60 + ( h.run_duration % 100 )
ELSE NULL
END AS Last_Run_Duration */
, CASE WHEN LEN(h.run_duration) > 6 THEN STUFF(STUFF(h.run_duration,LEN(h.run_duration)-3,0,':'),LEN(h.run_duration),0,':')
ELSE STUFF(STUFF(REPLACE(STR(h.run_duration,6,0),' ','0'),3,0,':'),6,0,':')
END AS Last_Run_Duration


FROM #tmp_enum_jobs t
LEFT JOIN msdb.dbo.sysjobs j (NOLOCK) ON t.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c (NOLOCK) ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h (NOLOCK) ON t.job_id = h.job_id
AND t.last_run_date = h.run_date
AND t.last_run_time = h.run_time
--AND h.step_id = 0
WHERE t.running = 1

DROP TABLE #tmp_enum_jobs;

Cheers,

~Leon
Post #1376266
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse