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

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server