How To Check if Job is Running?
-
Does anyone know how to write the T-SQL that checks if a job is still running? I need to make sure that a job has finished before executing some additional codes. I need to write something like this:
IF Job1 Is NOT Running
<Execute This and That etc..>
Thanks.
-
Check sp_help_job.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
EXEC SP_HELP_JOB
See the execution_status. If 1 then is running, and 4 if the job is idle (if I am not mistaken).
Regards,
kokyan 
-
Value Description
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
The above are the value for @execution_status. I'm reading the SP_HELP_JOB documentation and still trying to figure out how to use the result returned from SP_HELP_JOB in a conditional IF evaluation. Based on my testing, a return value of zero is "not executing", which is what I need. However, how to do this check in an IF statement is another problem.
SP_HELP_JOB returns what appears to be the result of 4 separate queries when you execute it. I don't see how I can extract the logic using T-SQL to determine if the @execution_status = 0.
Has anyone done this before? There must be some easy way to figure out if a job is executing or not...
-
One method is create a temporary table and insert all the results into it. Select the 'execution status' field and compare the result with the table you specified above.
I use the method above and solve my problem. I used it before to check my jobs status. If the job "down", then the store proc will up the job again.
Regards, kokyan -
I'm not taking the credit for this because somebody else on this site pointed me in this direction, but this is what I use:
-- Create a temporary table to hold job info
CREATE TABLE #xp_results (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, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
-- Am I sysadm and who am I
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()
-- Loop until the <job> stops
WHILE 1=1
BEGIN
-- Clear out temporary table on each pass
TRUNCATE TABLE #xp_results
-- Populate the temporary table with job details from Extended SP xp_sqlagent_enum_jobs
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
-- If the <job> is running then break out of the loop, otherwise wait for 10 secs
IF (SELECT COUNT(*)
FROM #xp_results xpr
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
msdb.dbo.sysjobs_view sjv
WHERE (sjv.job_id = xpr.job_id)
AND step_name = '<your job name>' and job_state = 1) = 0
BREAK
ELSE
WAITFOR DELAY '00:00:10'
END
drop table #xp_results
HTH
Andy
-
Someone else on this site sent me this useful script when I asked a similar question. You can use this in a conditional statement without building a temp table. It does run a little biut slowly, though (takes about 4 seconds on my server).
select J.Name as JobName, RP.program_name
from msdb..sysjobs J with (nolock)
inner join master..sysprocesses RP with (nolock)
on RP.program_name like 'SQLAgent - TSQL JobStep (Job ' + master.dbo.fn_varbintohexstr(convert(binary(16),J.job_id )) + '%'
order by JobName
-
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 A
WHERE
A.[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 1
ELSE
RETURN 0
* Noel -
CREATE PROCEDURE sp_IsJobRunning
@JobName as varchar(100) = Null
AS
-- Create a temporary table to hold job info
CREATE TABLE #Job (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, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
-- Get Job Info
-- xp_sqlagent_enum_jobs needs to be changed if user is not "sysadmin" role or owner of job is not "dbo"
-- In our environment, everything is owned by "dbo", and user account executing this is part of "sysadmin"
INSERT INTO #Job
EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo
-- Return Status of Job
-- Running : 1 (Running) or 0 (Not Running)
SELECT Running
FROM #Job J INNER JOIN msdb.dbo.sysjobs SJ ON
J.job_ID = SJ.job_ID
WHERE [name] = @JobName
The above is what I've ended up creating and using. I've copied bits and pieces from various suggestions. I had such a difficult time getting the SP_HELP_JOB to work with an INSERT statement that I gave up.
Noeld's suggestions works well, but I have a problem with hard-coding the ServerName, Username, and Password inside the proc. It's too much trouble to modify the proc whenever these 3 variable changes. Also, I work in a test environment, and it's troublesome to change the proc back and forth depending on which server it's going to.
Thanks everyone for your help. If anyone improves on this, please share! Thanks!
-
You could still use something similar to Noeld suggestion, just use OPENQUERY instead of OPENROWSET. Then you can use a linked server. A server can have a linked server that refers to itself.
Personally I like this method as it does away with the need for a temp table. Also, if MS decide to change the output of the xp then you have to re-do your temp table definition.
--------------------
Colt 45 - the original point and click interface
-
Do anyone know what is the name of the system tables that included in the store procedure sp_help_job. I can only get the name and other related info from msdb..sysjobs. Unfortunately, I can't get the execution status (1/4) from the table. I know there are join between different system tables in the store proc.
Regards, kokyan -
The execution status comes from sp_get_composite_job_info which in turn comes from xp_sqlagent_enum_jobs.
--------------------
Colt 45 - the original point and click interface -
Koyan,
Try running sp_helptext sp_help_job when you are in query analyzer in the msdb database.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Gary's idea is great! Thanks for it as I never think before that we can actually read what MS is running at our back!
Unfortunately, when I tracing, I end at xp_sqlagent_enum_jobs. I can't get what is in that store proc because it only call a DLL (spstar.dll). How do I get the idea what tables are using in that DLL?
Regards, kokyan -
if you look at sp_job_help, it's a wrapper proc for "sp_get_composite_job_info". If you are not using any parameters there is no danger in executing sp_get_composite_job_info directly. Much faster, too.
Also, if you look at that proc you see that a large portion of it is done by xp_sqlagent_enum_jobs. Read the proc and you can reverse engineer it pretty easy.
Signature is NULL
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply