Checking the status of SQL Server Agent jobs

Category : Scripts User Rating : 4.5 Stars      Views : 5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




One of the checks I like to do at the start of each day is to look at the status of all SQL agent jobs that ran overnight. Job Activity Monitor in Enterprise Manager is very useful for this, however it can be time consuming to use this, especially if you have a lot of servers. In this article I've outlined some scripts that I run against each production server in order to perform these checks.

Just in case you are not aware, Job Activity Monitor is available in SQL Server Management Studio under the 'SQL Server Agent' items in Object Explorer :

Job Activity Monitor

Double clicking on this will open up a new window containing details of all agent jobs.

Getting a Complete Job History

Now, onto the scripts. The following query lists all jobs run in the last 24 hours, along with the time run, the outcome, and whether the job is enabled :
USE MSDB
SELECT name AS [Job Name]
         ,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */ 
          +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */ 
          + (run_time - (run_time/100) * 100)  /* secs */
           ,CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]
         ,CASE WHEN enabled=1 THEN 'Enabled' 
               ELSE 'Disabled' 
          END [Job Status]
         ,CASE WHEN SJH.run_status=0 THEN 'Failed'
                     WHEN SJH.run_status=1 THEN 'Succeeded'
                     WHEN SJH.run_status=2 THEN 'Retry'
                     WHEN SJH.run_status=3 THEN 'Cancelled'
               ELSE 'Unknown' 
          END [Job Outcome]
FROM   sysjobhistory SJH 
JOIN   sysjobs SJ 
ON     SJH.job_id=sj.job_id 
WHERE  step_id=
AND    DATEADD(S, 
  (run_time/10000)*60*60 /* hours */ 
  +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */ 
  + (run_time - (run_time/100) * 100)  /* secs */, 
  CONVERT(DATETIME,RTRIM(run_date),113)) >= DATEADD(d,-1,GetDate()) 
ORDER BY name,run_date,run_time 
This uses the sysjobhistory and sysjobs system tables in the msdb database. One curious aspect of these tables is that they store dates and times as a numerical respresentation of a time and date such as 20120417. To make this more readable I've converted this into a more conventional format.

To obtain status information about each job you can run the following stored procedure, (this is actually what Job Activity Monitor uses) :
exec msdb.dbo.sp_help_job

Listing “Problem” Jobs

One disadvantage of the queries above is that they can generate a lot of rows, especially if you have jobs that run frequently. The following query is adapted from the one above, but has a maximum of one row per job. It generates a list of jobs that failed the most recent time that they were run (if the job failed and then succeeded on a subsequent run then it won’t be listed). This is useful for giving a "heads up" for potential problems to be investigated :
;WITH CTE_MostRecentJobRun AS 
 ( 
 -- For each job get the most recent run (this will be the one where Rnk=1) 
 SELECT job_id,run_status,run_date,run_time 
 ,RANK() OVER (PARTITION BY job_id ORDER BY run_date DESC,run_time DESC) AS Rnk 
 FROM sysjobhistory 
 WHERE step_id=
 ) 
SELECT  
  name  AS [Job Name]
 ,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */ 
  +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */ 
  + (run_time - (run_time/100) * 100)  /* secs */, 
  CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]
 ,CASE WHEN enabled=1 THEN 'Enabled' 
     ELSE 'Disabled' 
  END [Job Status]
FROM     CTE_MostRecentJobRun MRJR 
JOIN     sysjobs SJ 
ON       MRJR.job_id=sj.job_id 
WHERE    Rnk=
AND      run_status=0 -- i.e. failed 
ORDER BY name 

Listing Running Jobs

Finally it’s useful to check on jobs that are still running, especially if they have been running for a long time. The following query does just that :
exec msdb.dbo.sp_help_job @execution_status=1
As an alternative you can also use the undocumented procedure xp_sqlagent_enum_jobs to do this. Note that you might need to use sp_configure to switch on Agent XPs in order to run this. The following query also returns information about running queries. As always with using undocumented stored procedures it should be used appropriately !
IF OBJECT_ID('tempdb.dbo.#RunningJobs') IS NOT NULL
      DROP TABLE #RunningJobs
CREATE TABLE #RunningJobs (  
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 #RunningJobs EXEC master.dbo.xp_sqlagent_enum_jobs 1,garbage  
 
SELECT    
  name AS [Job Name]
 ,CASE WHEN next_run_date=0 THEN '[Not scheduled]' ELSE
   CONVERT(VARCHAR,DATEADD(S,(next_run_time/10000)*60*60 /* hours */ 
  +((next_run_time - (next_run_time/10000) * 10000)/100) * 60 /* mins */ 
  + (next_run_time - (next_run_time/100) * 100)  /* secs */, 
  CONVERT(DATETIME,RTRIM(next_run_date),112)),100) END AS [Start Time]
FROM     #RunningJobs JSR 
JOIN     msdb.dbo.sysjobs 
ON       JSR.Job_ID=sysjobs.job_id 
WHERE    Running=1 -- i.e. still running 
ORDER BY name,next_run_date,next_run_time 
All the above queries have been tested on SQL Server 2005, 2008, 2008 R2 and 2012.
Link back to this article : http://www.sqlmatters.com/Articles/Checking the status of SQL Server Agent jobs.aspx

Keywords

SQL,scripts,TSQL,Agent,status


Comments
Post by Himanshu Sharma on Wed 23 Oct 2013 10:01. Report Inappropriate Post

Awesome article. Helped a lot.
Post by Himanshu Sharma on Wed 23 Oct 2013 10:01. Report Inappropriate Post

Awesome article. Helped a lot.
Post by vim on Wed 23 Oct 2013 11:16. Report Inappropriate Post

This is good , But how to find all Job History in all server , Without linked server
Post by bramha on Wed 23 Oct 2013 14:04. Report Inappropriate Post

Awesome..More help full for me
Post by Olia on Wed 18 Dec 2013 15:12. Report Inappropriate Post

great job
Post by ted on Mon 17 Feb 2014 23:58. Report Inappropriate Post

Worked Perfectly.
Absolutely wonderful!

Thanks!
Post by Chris on Wed 18 Jun 2014 17:20. Report Inappropriate Post

I didn't think you could do this:
IF OBJECT_ID('tempdb.dbo.#RunningJobs') IS NOT NULL

Tables are created in tempdb with a random name, with a long string of underscores followed by a random 12 characters.
use tempdb;
select name from sys.tables where name like '%Runningjobs%';

#Runningjobs________________________________________________________________________________________________________00000009C8A2

So this check for that table will never show it existing?
Post by Matt on Wed 25 Jun 2014 15:22. Report Inappropriate Post

Chris,

if you are still connect to the database with the same SPID, the statement... IF OBJECT_ID('tempdb.dbo.#RunningJobs') IS NOT NULL will work because that SPID connection still exists, thus the temp table still exists.

The #RunningJobs table name is just an alias name to the true actual longer name in the TempDB.



Post by Roushan on Sat 25 Oct 2014 10:10. Report Inappropriate Post

Hey, many thanks. please let me know how to retrive the data only of failed jobs of last 30 days or between two dates.
Post by ABHINAV SINgh on Mon 17 Nov 2014 23:58. Report Inappropriate Post

Hi,

Thank you very much for the above script.
Is it possible to change the job history script to get Job history for more than 24 hrs?
If yes, the kindly help on how to edit the query.
Post by Jack Ellison on Mon 26 Jan 2015 19:48. Report Inappropriate Post

Using a global temp table, eg ##RunningJobs, should work to keep its visibility for any spid
Post by afzal khan on Wed 11 Mar 2015 07:26. Report Inappropriate Post

awesome, help me lot
Post by karim on Thu 19 Mar 2015 15:38. Report Inappropriate Post

very like :)
Post by harikrishna on Mon 11 May 2015 17:22. Report Inappropriate Post
Website : http://www.gmail.com
its awesome and helped me a lot
Post by Tom Pester on Wed 13 May 2015 08:56. Report Inappropriate Post

Tanks for the nice post. You can use dbo.agent_datetime(run_date, run_time) to get rid of tedious math.
Post by Wahiddin ishak on Tue 01 Sep 2015 03:15. Report Inappropriate Post
Website : http://wahiddinishak.com
Thanks !
Post by sorineatza on Wed 27 Jan 2016 07:37. Report Inappropriate Post

great!
Post by Deepak on Thu 25 Feb 2016 11:36. Report Inappropriate Post

Great post!

I am using always ON configuration. I would like to read all the job agent job details and add to to one table, so that during failover I can read from that table and reconfigure the jobs because jobs are not getting synchronized during fail over. Kindly guide
Post by amin on Wed 11 May 2016 21:12. Report Inappropriate Post

FYI. To make it even more useful, if required, this can be run on all the servers, in one execution, via Central Management Servers.
Post by Vivek on Fri 11 Nov 2016 11:37. Report Inappropriate Post

God bless you ....Thanks
Post by madhu on Fri 02 Dec 2016 15:14. Report Inappropriate Post

really good one, learnt new thing today, thx for sharing this

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered