How To Check if Job is Running?

Question

Post reply

How To Check if Job is Running?

Viewing 6 posts - 16 through 20 (of 20 total)

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

Calvin Lawson SSChampion Points: 11030 More actions · Answer 1

Oops! wrong forum post. I was on the same track you guys were, though...so that's good

Signature is NULL

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

noeld - Wednesday, February 11, 2004 10:41 AM
how about:
CREATE PROCEDURE IsJobRunning @JobName as varchar(100) = Null as
--check!!
if (@JobName IS NULL) RETURN -1
Declare @theID as uniqueidentifier
--get the ID
Select @theID = A.job_id FROM OPENROWSET('SQLOLEDB','dbServerName'; 'Uname'; 'pwd' , 'SET FMTONLY OFF exec msdb.dbo.sp_help_job') as AWHEREA.[name]= @JobName
if EXISTS(Select * FROM OPENROWSET('SQLOLEDB','dbServerName'; 'Uname'; 'pwd' , 'SET FMTONLY OFF exec master.dbo.xp_sqlagent_enum_jobs 1,dbo') as A WHERE A.[Job ID] = @theID AND A.Running = 1 )
RETURN 1ELSE RETURN 0

I know this is a wicked old post and I don't know if it ever worked because this is the first time I've seen this post, but OPENROWSET doesn't currently work with anything that hits on an extended stored procedure because it can't determine what the meta-data is. Here's the error that I get...

Msg 11519, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 14]
The metadata could not be determined because statement 'EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo' invokes an extended stored procedure (DLL).
Of course, I might just be doing something wrong. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Jeff Moden - Tuesday, May 22, 2018 3:26 PM
I know this is a wicked old post and I don't know if it ever worked because this is the first time I've seen this post, but OPENROWSET doesn't currently work with anything that hits on an extended stored procedure because it can't determine what the meta-data is. Here's the error that I get...
Msg 11519, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 14]
The metadata could not be determined because statement 'EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo' invokes an extended stored procedure (DLL).
Of course, I might just be doing something wrong. 😀

I get the same error but I'm pretty sure this used to work back in the day. I remember scripts with the openrowset for this very thing - pretty sure I have one somewhere.
But then the definition of the result set for xp_sqlagent_enum_jobs ended up being posted in quite a few places so it really wasn't needed. I guess now I should post it since you revived a 30 year old thread and someone may want it 🙂
This is the definition I have for the results set for xp_sqlagent_enum_jobs. It still works (in the year 2018) and the spacing should be a mess since that's how this forum software is:

CREATE TABLE #AgentEnumJobs (job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, request_source INT NOT NULL, request_source_id sysname COLLATE database_default NULL, running INT NOT NULL, current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL)

Sue

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

Sue_H - Tuesday, May 22, 2018 6:35 PM
Jeff Moden - Tuesday, May 22, 2018 3:26 PM
I know this is a wicked old post and I don't know if it ever worked because this is the first time I've seen this post, but OPENROWSET doesn't currently work with anything that hits on an extended stored procedure because it can't determine what the meta-data is. Here's the error that I get...
Msg 11519, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 14]
The metadata could not be determined because statement 'EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo' invokes an extended stored procedure (DLL).
Of course, I might just be doing something wrong. 😀
I get the same error but I'm pretty sure this used to work back in the day. I remember scripts with the openrowset for this very thing - pretty sure I have one somewhere.
But then the definition of the result set for xp_sqlagent_enum_jobs ended up being posted in quite a few places so it really wasn't needed. I guess now I should post it since you revived a 30 year old thread and someone may want it 🙂
This is the definition I have for the results set for xp_sqlagent_enum_jobs. It still works (in the year 2018) and the spacing should be a mess since that's how this forum software is:
CREATE TABLE #AgentEnumJobs (job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, request_source INT NOT NULL, request_source_id sysname COLLATE database_default NULL, running INT NOT NULL, current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL)
Sue

Thanks for the confirmation Sue. Heh... I went digging in my OPENROWSET archives and found many code examples where calls to XPs used to work just fine. Must be one of the "improvements" that MS made. Just for the sake of curiosity, I have an old 2005 box at work. I'll see if OPENROWSET on xp_sqlagent_enum_jobs there.

Heh... Unfortunately, this is yet again another proof that "Change is inevitable... change for the better is not." 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Jeff Moden - Tuesday, May 22, 2018 9:06 PM
Thanks for the confirmation Sue. Heh... I went digging in my OPENROWSET archives and found many code examples where calls to XPs used to work just fine. Must be one of the "improvements" that MS made. Just for the sake of curiosity, I have an old 2005 box at work. I'll see if OPENROWSET on xp_sqlagent_enum_jobs there.
Heh... Unfortunately, this is yet again another proof that "Change is inevitable... change for the better is not." 😀

I was going to try the oldest I have (2008) since I'm curious about when it broke. Just wondering if they killed that functionality with openrrowset when they introduced the resource database and started all of the openrowset calls that databases. Seems like they would have made changes to get the behaviors with those calls.

Sue

Nathan SSChasing Mays Points: 631 More actions · Answer 6

I think there's a much easier way, as provided by Microsoft here:

https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobactivity-transact-sql?redirectedfrom=MSDN&view=sql-server-2017#example

SELECT sj.Name, 
CASE
WHEN sja.start_execution_date IS NULL THEN 'Not running'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
END AS 'RunStatus'
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
WHERE session_id = (
SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity);