SQL Server Agent Job Timings
DECLARE @Table TABLE
(JobID VARCHAR(2000),Jobname VARCHAR(1000),LastRunDatetime VARCHAR(100),TimeAlone VARCHAR(100),
LastRunStatus
varchar(100),LastDuration varchar(100),LastRunMessage NVARCHAR(4000),NextRundate NVARCHAR(4000))
INSERT INTO @Table
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, CASE
WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
ELSE CAST(
CAST([sJOBH].[run_date] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [LastRunDateTime]
,convert(char(50),CASE
WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
ELSE CAST(
CAST([sJOBH].[run_date] AS CHAR(8))
+ ' ' + STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END,108) AS [Timealone]
, CASE [sJOBH].[run_status]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'Running' -- In Progress
END AS [LastRunStatus]
, STUFF(STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':'), 6, 0, ':')
AS [LastRunDuration (HH:MM:SS)] , [sJOBH].[message] AS [LastRunStatusMessage]
, CASE [sJOBSCH].[NextRunDate]
WHEN 0 THEN NULL
ELSE CAST(CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
+ ' '
+ STUFF(STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':')
AS DATETIME)
END AS [NextRunDateTime]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN (SELECT [job_id]
, MIN([next_run_date]) AS [NextRunDate]
, MIN([next_run_time]) AS [NextRunTime]
FROM [msdb].[dbo].[sysjobschedules]
GROUP BY [job_id]) AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id] LEFT JOIN
(SELECT [job_id],[run_date],[run_time]
, [run_status]
,[run_duration]
, [message]
, ROW_NUMBER() OVER (PARTITION BY [job_id]
ORDER BY [run_date] DESC, [run_time] DESC) AS RowNumber
FROM [msdb].[dbo].[sysjobhistory]
WHERE [step_id] = 0 ) AS [sJOBH]
ON [sJOB].[job_id] = [sJOBH].[job_id]
AND [sJOBH].[RowNumber] = 1
ORDER BY [JobName]
SELECT * FROM @Table
where year(LastRunDatetime)='2018'
and month(LastRunDatetime)=06
and day(LastRunDatetime)=12
and TimeAlone<'09:30:00'
Comments