Skip to main content

Check SQL Server Job status (State) using sp_help_job and xp_sqlagent_enum_jobs

This article is about checking the status of a SQL job. In our work place we have lot of SQL jobs. These jobs will run whole day and are business critical. They will load the data and generate extracts which will be used by business people. Thus, it becomes quite essential to support the system efficiently so that the job finishes in time and as desired. Also, while designing a new system sometimes we need to check the dependency of one job over another. In such scenario we need to check whether a particular job has finished or not.

All this can be achieved in SQL Server by using the procedures:-

sp_help_job
xp_sqlagent_enum_jobs

Note: xp_sqlagent_enum_jobs is an undocumented proc inside of sp_help_job and is used extensively to get SQL agent job information.

sp_help_job:
This procedure gives some insight into the status, and information, about a job. This stored procedure provides information such as last start time, job status etc.

Syntax
sp_help_job { [ @job_id= ] job_id
[ @job_name= ] 'job_name' }
[ , [ @job_aspect= ] 'job_aspect' ]
[ , [ @job_type= ] 'job_type' ]
[ , [ @owner_login_name= ] 'login_name' ]
[ , [ @subsystem= ] 'subsystem' ]
[ , [ @category_name= ] 'category' ]
[ , [ @enabled= ] enabled ]
[ , [ @execution_status= ] status ]
[ , [ @date_comparator= ] 'date_comparison' ]
[ , [ @date_created= ] date_created ]
[ , [ @date_last_modified= ] date_modified ]
[ , [ @description= ] 'description_pattern' ]

The sp_help_job stored procedure has a parameter named execution_status that can be used to return information for all jobs that have a particular status. For example, command to retrieve job information for all jobs that currently have a status of "Executing":

exec msdb.dbo.sp_help_job @execution_status = 1

Like this you can pass different arguments to retrieve different information. You can find more information on this here.


xp_sqlagent_enum_jobs
It is an extended stored procedure it helps in identifying long running job or to start and monitor a job to completion using T-SQL or to monitor a long running SQL job and generate alerts based on that information.


Here is how you can use this stored procedure:

begin
declare @running char(1)
select @running = 'Y' -- value
declare @run int
declare @status int

select @run=case when @running='Y' then 1 else 0 end

select @status=case when @running='S' then 1 when @running='F' then 0 when @running='C' then 3 else 4 end

create table #enum_job (Job_ID uniqueidentifier,

Last_Run_Date int,

Last_Run_Time int,

Next_Run_Date int,

Next_Run_Time int,

Next_Run_Schedule_ID int,

Requested_To_Run int,

Request_Source int,

Request_Source_ID varchar(100),

Running int,

Current_Step int,

Current_Retry_Attempt int,

State int)

insert into #enum_job

exec master.dbo.xp_sqlagent_enum_jobs 1,garbage

begin

select distinct case
when state=1 then 'Executing'
when state=2 then 'Waiting for thread'
when state=3 then 'Between retries'
when state=4 then 'Idle'
when state=5 then 'Suspended'
when state=7 then 'Performing completion actions'
end as State,

sj.name,

case when ej.running=1 then st.step_id else 0 end as currentstepid,

case when ej.running=1 then st.step_name else 'not running' end as currentstepname,

ej.request_source,

ej.request_source_id,

case when ej.running=1 then st.command else '' end as stepcommand,

sjh.run_status,

case
when sjs.last_run_outcome=1 then 'Succeeded (' + cast (isnull(sjs.last_run_date,'') as varchar) +':' + cast(isnull(sjs.last_run_time,'') as varchar) +')'
when sjs.last_run_outcome=0 then 'Failed (' + cast (isnull(sjs.last_run_date,'') as varchar) +':' + cast(isnull(sjs.last_run_time,'') as varchar) +')'
when sjs.last_run_outcome=3 then 'cancelled (' + cast (isnull(sjs.last_run_date,'') as varchar) +':' + cast(isnull(sjs.last_run_time,'') as varchar) +')'
when sjs.last_run_outcome=5 then 'Unknown' end as last_status,

sjh.message,

sj.job_id

from #enum_job ej
join msdb..sysjobs sj on sj.job_id=ej.job_id
join msdb..sysjobsteps st on st.job_id=ej.job_id and (st.step_id=ej.current_step or ej.current_step=0)
left join msdb..sysjobhistory sjh on sjh.job_id=sj.job_id
left join msdb..sysjobservers sjs on sjs.job_id=sj.job_id
where ej.running+1>1

and (sjh.instance_id=(select max(instance_id) from msdb..sysjobhistory where job_id=ej.job_id ) or sjh.instance_id is null)

and ((sjs.last_run_outcome=@status or @status=4))

order by sj.state desc, sj.name
end

drop table #enum_job
end


Tweak the code to suit your requirements.

Hope this will help you.

Comments

  1. One of the effective and correct way to get the job Running Status report in SSRS for easy viewing and monitoring jobs running on servers. (.rdl file attached.)
    http://www.sqllion.com/2011/11/job-running-status-report-in-ssrs/

    ReplyDelete
  2. Really very useful and at the same time much needed information you had provided here. And if you are trying for the better placement career you may choose with our Hr Consultancy in Chennai

    ReplyDelete

Post a Comment

Popular posts from this blog

Laravel XAMPP MySQL artisan migrate install error mysql.sock

In my previous post I wrote about setting up Laravel 4 on Mac with XAMPP . When I tried to use migrations (using artisan) I faced some issue. I will also post about Laravel migrations soon.    php artisan migrate:install Error :                                                     [PDOException]                                       SQLSTATE[HY000] [2002] No such file or directory                                              Solution : We need to tell artisan which mysql we want it to use. For this to work we need to porivde it with mysql.sock which we want it to use. So change your database settings like this: 'mysql' => array( 'driver'    => 'mysql', 'host'      => 'localhost',     'unix_socket' => '/Applications/xampp/xamppfiles/var/mysql/mysql.sock', 'database'  => 'wedding', 'username'  => 'root', 'password'  => '', '

Add (Drop) a new article (table) at publisher in merge replication

Let us add the article using the GUI first. First of all create the table on publisher database. Now right click on the publisher and select properties from the menu. Click on articles. Uncheck the - "Show only checked articles in the list" checkbox, in order to see the newly added table. Select the table as shown in the figure below. Press ok The article has now been added to the publisher We now need to recreate the snapshot Right click the publication and select – “View snapshot agent status”. Click start to regenerate snapshot. Now right click on the subscription (I have both on same server you may have on different servers) and select “View synchronization status” Click on start on the agent. The schema changes will propagate to the client if you have "Replicate schema changes" property set to true in the publisher.