Below is a T-SQL script to check SQL job run status.
SET NOCOUNT ON
--Checking for SQL Server verion
IF CONVERT(tinyint,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1))) <> 8
BEGIN
---This is for SQL 2k5 and SQL2k8 servers
SET NOCOUNT ON
SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,
j.name AS job_name,
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,
CASE jh.run_status WHEN 0 THEN 'Error Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress' ELSE
'Status Unknown' END AS 'last_run_status',
ja.run_requested_date as last_run_date,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
ja.next_scheduled_run_date,
CONVERT(VARCHAR(500),jh.message) AS step_description
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity) ORDER BY job_name,job_status
END
ELSE
BEGIN
--This is for SQL2k servers
SET NOCOUNT ON
DECLARE @SQL VARCHAR(5000)
--Getting information from sp_help_job to a temp table
SET @SQL='SELECT job_id,name AS job_name,CASE enabled WHEN 1 THEN ''Enabled'' ELSE ''Disabled'' END AS job_status,
CASE last_run_outcome WHEN 0 THEN ''Error Failed''
WHEN 1 THEN ''Succeeded''
WHEN 2 THEN ''Retry''
WHEN 3 THEN ''Cancelled''
WHEN 4 THEN ''In Progress'' ELSE
''Status Unknown'' END AS last_run_status,
CASE RTRIM(last_run_date) WHEN 0 THEN 19000101 ELSE last_run_date END last_run_date,
CASE RTRIM(last_run_time) WHEN 0 THEN 235959 ELSE last_run_time END last_run_time,
CASE RTRIM(next_run_date) WHEN 0 THEN 19000101 ELSE next_run_date END next_run_date,
CASE RTRIM(next_run_time) WHEN 0 THEN 235959 ELSE next_run_time END next_run_time,
last_run_date AS lrd, last_run_time AS lrt
INTO ##jobdetails
FROM OPENROWSET(''sqloledb'', ''server=(local);trusted_connection=yes'', ''set fmtonly off exec msdb.dbo.sp_help_job'')'
exec (@SQL)
--Merging run date & time format, adding run duration and adding step description
select Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,jd.job_name,jd.job_status,jd.last_run_status,
CONVERT(DATETIME,RTRIM(jd.last_run_date)) +(jd.last_run_time * 9 + jd.last_run_time % 10000 * 6 + jd.last_run_time % 100 * 10) / 216e4 AS last_run_date,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
CONVERT(DATETIME,RTRIM(jd.next_run_date)) +(jd.next_run_time * 9 + jd.next_run_time % 10000 * 6 + jd.next_run_time % 100 * 10) / 216e4 AS next_scheduled_run_date,
CONVERT(VARCHAR(500),jh.message) AS step_description
from (##jobdetails jd LEFT JOIN msdb.dbo.sysjobhistory jh ON jd.job_id=jh.job_id AND jd.lrd=jh.run_date AND jd.lrt=jh.run_time) where step_id=0 or step_id is null
order by jd.job_name,jd.job_status
--dropping the temp table
drop table ###jobdetails
END
Leave a Reply