Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to find that job is running?

By Leo Peysakhovich,

After my article publication in SQLServerCentral.com  http://www.sqlservercentral.com/columnists/lPeysakhovich/controllingunusuallylongrunningjobs.asp 

I received few emails from the readers who informed me about misleading part in the article.   

“In addition, changes can be done to help analyze those job steps which are still running but duration of the step is already unusually long. It can be achieved by adding additional criteria to the select statement for the field run_status. 

Simply exchange line

and soh.run_status = 1 -- successful jobs

with line

and ( soh.run_status = 1 or soh.run_status = 4 ) -- successful and in progress jobs “

I am giving my apologies to the public, because I didn’t check this statement but simply rely on the Microsoft documentation in book on line (BOL). In reality, SQL Server is not using this flag to measure the job progress. This is will be one of the many reasons for me to check all the statements and BOL next time. But for sake of error correction and to finalize the previous article I would like to show the way of checking a situation to define if job is running.

In addition, there is an article written by Gregory A. Larsen. http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2  His article shows how to find that any job is running.  But to give the answer "Is job running?" for the particular job the article is using undocumented stored procedure xp_sqlagent_enum_jobs. Microsoft SQL Server has always had nifty undocumented functions that people are tempting to use. However, Microsoft has always recommended not using those functions because they can change in future releases. Using undocumented features rarely burning DBA. But with SQL Server 2005 it may be the issue. 

The usage of  system stored procedure sp_help_job which returns information about jobs that are used by SQLServerAgent service to perform automated activities in Microsoft® SQL Server™  has some limitations if you need to find the answer for the particular job. (Read details in Gregory A. Larsen article. See the link above). Is there any way  to find the  state of a particular job without usage of undocumented stored procedure? This is the article about.

Let’s assume that a job has few steps. Those steps are written in system table sysjobsteps in database msdb. When a job is running SQL Server is writing each ended step into msdb..sysjobhistory. By getting the last step, time, and status of the step it is possible to get the status of the job. First, we need to know the last time the job was running. Programmatically, you can analyze the job schedule, but it is not very reliable way because sometimes the job can be enforced to run manually. The best way is to look for the keyword '(Job outcome)' in sysjobhistory table because this is a keyword of the last entered step for the job in the table  and the entry should have the maximum value in the column instance_id. Instance_id is numeric column in the table sysjobhistory and gets always (max + 1 ) value. Next statement will return all the steps the job already finished during the current run.

STATEMENT 1

select * from dbo.sysjobhistory sjh
	inner join sysjobs sj 
		on sj.job_id = sjh.job_id
 where sj.name = 'job1'  
 and sjh.instance_id >  (select max(instance_id)     from sysjobhistory  sjh1
                                       where sjh1.step_name = '(Job outcome)' and sjh1.job_id = sj.job_id )
													

As always, there are some obstacles and issues. If the statement returns no rows there are two possible logical outcomes. Or job is done, or job is running the very first step. Again the dilemma - how to know what is true? In the case you really need to know the answer reliably, my suggestion would be to add the first step for a job as a quick running fake step. For example, step will consists of one statement   SELECT getdate(). This step whether failed or succeeded  will go to the next job step and table sysjobhistory will get the first step entry indicating that the job is started. This row will be the outcome of the query above and let us know that the job is started and running. For all SQL statements below there is an assumption that all jobs have a fake initial step. Otherwise, the behavior of those statements may be different if job is running step 1. 

Next issue is to get the step where job is right now. Based on the statement above we know that the job is running and the steps that is successfully finished or failed. The determination of the next running step is related to the output values from table sysjobsteps. Based on SQL Server BOL (verified) next definitions can be made.

[@on_success_action =] success_action

Is the action to perform if the step succeeds. success_action is tinyint, and can be one of these values.

Value

Description (action)

1 (default)

Quit with success

2

Quit with failure

3

Go to next step

4

Go to step on_success_step_id

 [@on_success_step_id =] success_step_id

Is the ID of the step in this job to execute if the step succeeds and success_action is 4. success_step_id is int, with a default of 0.

[@on_fail_action =] fail_action

Is the action to perform if the step fails. fail_action is tinyint, and can be one of these values.

Value

Description (action)

1

Quit with success

2 (default)

Quit with failure

3

Go to next step

4

Go to step on_fail_step_id

 [@on_fail_step_id =] fail_step_id

Is the ID of the step in this job to execute if the step fails and fail_action is 4. fail_step_id is int, with a default of 0.

Next batch allows to return information about the next running step.

BEGIN
declare @step_id int, @run_status int, @next_runstep int, @jobname varchar(128), @jobid uniqueidentifier  
declare @step_action int , @next_step_id int

set @jobname = 'job1'  
-- get the job id
select @jobid = job_id  from sysjobs  where name = @jobname  
-- Find the last running step
select @step_id = max(step_id)  from dbo.sysjobhistory sjh  where job_id = @jobid 
 and sjh.instance_id >  (select max(instance_id)  from sysjobhistory  sjh1
                                      where sjh1.step_name = '(Job outcome)' and sjh1.job_id = @jobid )  
-- Get the step status
select @run_status = run_status
 from sysjobhistory sjh
 where job_id = @jobid and step_id = @step_id
 and instance_id > (select max(instance_id)
 from sysjobhistory  sjh1
 where sjh1.step_name = '(Job outcome)' and sjh1.job_id = @jobid)   

-- get the action for the last finished step
select @step_action = CASE
      WHEN @run_status = 1 THEN on_success_action
      ELSE on_fail_action
    END ,
    @next_step_id  = CASE     
         WHEN @run_status = 1 THEN on_success_step_id
         ELSE on_fail_step_id
    END 
 from msdb.dbo.sysjobsteps   
 where job_id = @job_id and step_id = @step_id

-- Based on the status find the next step job is running
select @next_runstep = CASE
     WHEN @step_action in (1,2) THEN @step_id
     -- this is the last step
     WHEN @step_action = 3 THEN @step_id + 1
     -- next step
     WHEN @step_action = 4 THEN @next_step_id
     -- Go to step on_fail_step_id
     ELSE -1
     -- unknown step
  END
SELECT 'Step number ' + cast(@next_runstep as varchar) + ' is running for the job ' + @jobname

END

Now, based on the batch above, we can find the actual step job is running and get an answer if job is really running. It is looks logically to create a stored procedure if you need the information about the step the job is running. 

If the question is only “Whether the job is running?” then the best choice is to use the first statement (STATEMENT 1) and based on the outcome (returned number of rows) get the answer. If there are no rows returned then the job is not running. Otherwise job is running. 

What if the requirement is to find whether the any job is running or not? For example, before the server automated weekly maintenance started it may necessary to have this information. 

select sj.job_id, sj.name, sjh.step_name from dbo.sysjobhistory sjh
  inner join sysjobs sj  on sj.job_id = sjh.job_id
  where sjh.instance_id >   (select max(instance_id)    from sysjobhistory  sjh1
                    where sjh1.step_name = '(Job outcome)' 
						  and sjh1.job_id = sjh.job_id
                    )

And the only issue left with the jobs that run the first time. They don’t have '(Job outcome)' row(s) yet. Next query will combine together both scenarios for one particular job. 

IF exists ( select * from dbo.sysjobhistory sjh   
				 inner join sysjobs sj  on sj.job_id = sjh.job_id
            where sj.name = 'job3'  
				and step_name = '(Job outcome)' ) 

select * from dbo.sysjobhistory sjh  
	inner join sysjobs sj  
	on sj.job_id = sjh.job_id
 where sj.name = 'job3' 
 and sjh.instance_id > (select max(instance_id)
                                 from sysjobhistory  sjh1
                                 where sjh1.step_name = '(Job outcome)' and sjh1.job_id = sj.job_id ) 
ELSE
 select * from dbo.sysjobhistory sjh 
    inner join sysjobs sj on sj.job_id = sjh.job_id 
  where sj.name = 'job3'

Or to see any running job steps: 

select sj.job_id, sj.name, sjh.step_name
  from dbo.sysjobhistory sjh 
    inner join sysjobs sj  on sj.job_id = sjh.job_id
 where sjh.instance_id >   (select max(instance_id)   from sysjobhistory sjh1
                                              where sjh1.step_name = '(Job outcome)' 
                                              and sjh1.job_id = sjh.job_id ) 
union
select sj.job_id, sj.name, sjh.step_name from dbo.sysjobhistory sjh
    inner join sysjobs sj  on sj.job_id = sjh.job_id
 where sjh.step_name <> '(Job outcome)'
 and  sjh.instance_id =   ( select max(instance_id)
                                            from sysjobhistory  sjh1
                                            where sjh1.job_id = sjh.job_id )

Conclusion

As an example, even software companies (surprise!) have the outdated information in the documentation. So, always check your statements to avoid the code multifunction that may lead to some degree disaster. 

Total article views: 27324 | Views in the last 30 days: 55
 
Related Articles
FORUM

Job Monitor outcome results doubt

Outcome results in Sheduled jobs

FORUM

Drill through action in MSAS 2008

When executing action error returned "The selected action cannot be completed because of the followi...

FORUM

Analysis Services 2005 Drillthrough Action

AS 2005 Drillthrough Action / Rowset Action

FORUM

Try Success

Success block?

FORUM

Step fails, but job reports success.

Multiple step job still reports success even if any step but last step fails.

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones