How to Obtain All SQL Server Jobs Details

Issue
How to obtain all the details of all SQL Jobs created on a SQL server instance.

Product Line

Power Monitoring Expert 7.2.2
Power Monitoring Expert 8.2
Power Monitoring Expert 9.0
Power Monitoring Expert 2020
Power Monitoring Expert 2021
Power Monitoring Expert 2022
Power Monitoring Expert 2023
Power Monitoring Expert 2024

Environment
Microsoft SQL Server

SQL Server Management Studios

Cause
For daily monitoring purposes or for troubleshooting SQL job issues, obtaining information about the SQL jobs via a single query can be a very useful and time-saving alternative method.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

This query will provide the SQL Server job details for all jobs present.  It will provide the job name, job status, Last run date of the job & job execution status (failed or successful)
It may also be modified to add or change the fields reported as per the requirement.

To obtain these job details, log into SQL Server Management Studio and run the attached SQL script (SQL_Job_Details.sql).
Alternatively, execute the query(s) below:

Use msdb 
go 
select distinct j.Name as "Job Name", --j.job_id, 
case j.enabled  
when 1 then 'Enable'  
when 0 then 'Disable'  
end as "Job Status", jh.run_date as [Last_Run_Date(YY-MM-DD)] ,  
case jh.run_status  
when 0 then 'Failed'  
when 1 then 'Successful'  
when 2 then 'Retry' 
when 3 then 'Cancelled'  
when 4 then 'In Progress'  
end as Job_Execution_Status 
from sysJobHistory jh, sysJobs j 
where j.job_id = jh.job_id and jh.run_date =   
(select max(hi.run_date) from sysJobHistory hi where jh.job_id = hi.job_id )-- to get latest date

게시 대상: 슈나이더 일렉트릭 Korea