To Get Latest SQL Job Status

, 2003-09-04

This query will give you the current job status of all scheduled jobs on sql server. Just by using sysJobs, and sysJobHistory table you will get to know the details
about the last status of the scheduled jobs, irrespective of their status like failed, successful, or cancelled.

Note: You must have admin right on MSDB database in order to execute this query.

Mohit Nayyar
MCP, MCAD, MCSD, MCDBA

/*
SQL Job Status

This query will give you the current job status of all scheduled jobs on sql server.

Just by using sysJobs, and sysJobHistory table you will get to know the details about the last status of the scheduled jobs, irrespective of their status like failed, successful, or cancelled.

Note: You must have admin right on MSDB database in order to execute this query.

Mohit Nayyar
MCP, MCAD, MCSD, MCDBA
*/

Use msdb
go

select distinct j.Name as "Job Name", j.description as "Job Description", h.run_date as LastStatusDate, 
case h.run_status 
when 0 then 'Failed' 
when 1 then 'Successful' 
when 3 then 'Cancelled' 
when 4 then 'In Progress' 
end as JobStatus
from sysJobHistory h, sysJobs j
where j.job_id = h.job_id and h.run_date = 
(select max(hi.run_date) from sysJobHistory hi where h.job_id = hi.job_id)
order by 1

Rate

3.54 (13)

Share

Share

Rate

3.54 (13)

Related content

View/Edit ASP Script from From SQL Server 7.0/2000

Ever want to display (and edit) ASP code from the original .asp file?  (like PHP's PHPInfo() function).To setup:  * Just create a .asp with SQL Server connectivity.  * Change @IISHostNameWWWPath to match your servername      -OR-     if SQL Server is on the same machine, use the next     line instead. (see […]

2002-04-22

510 reads