Check Running Jobs

/*

Check Running Jobs

*/

SELECT

    [sJOB].[job_id] AS [JobID]

    , [sJOB].[name] AS [JobName]

    , CASE [sJSTP].[last_run_date]

        WHEN 0 THEN NULL

        ELSE

            CAST(

                CAST([sJSTP].[last_run_date] AS CHAR(8))

                + ' '

                + STUFF(

                    STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_time] AS VARCHAR(6)),  6)

                        , 3, 0, ':')

                    , 6, 0, ':')

                AS DATETIME)

      END AS [LastRunDateTime]

    , [sJSTP].[step_uid] AS [StepID]

    , [sJSTP].[step_id] AS [StepNo]

    , [sJSTP].[step_name] AS [StepName]

    , CASE [sJSTP].[last_run_outcome]

        WHEN 0 THEN 'Failed'

        WHEN 1 THEN 'Succeeded'

        WHEN 2 THEN 'Retry'

        WHEN 3 THEN 'Canceled'

        WHEN 5 THEN 'Unknown'

      END AS [LastRunStatus]

    , STUFF(

            STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_duration] AS VARCHAR(6)),  6)

                , 3, 0, ':')

            , 6, 0, ':')

      AS [LastRunDuration (HH:MM:SS)]

    , [sJSTP].[last_run_retries] AS [LastRunRetryAttempts]

FROM

    [msdb].[dbo].[sysjobsteps] AS [sJSTP]

    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]

        ON [sJSTP].[job_id] = [sJOB].[job_id]

where [sJOB].[name] = 'DAILY_'

ORDER BY [JobName], [StepNo]

SELECT

    [sJOB].[job_id] AS [JobID]

    , [sJOB].[name] AS [JobName]

    , [sJSTP].[step_uid] AS [StepID]

    , [sJSTP].[step_id] AS [StepNo]

    , [sJSTP].[step_name] AS [StepName]

    , CASE [sJSTP].[subsystem]

        WHEN 'ActiveScripting' THEN 'ActiveX Script'

        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'

        WHEN 'PowerShell' THEN 'PowerShell'

        WHEN 'Distribution' THEN 'Replication Distributor'

        WHEN 'Merge' THEN 'Replication Merge'

        WHEN 'QueueReader' THEN 'Replication Queue Reader'

        WHEN 'Snapshot' THEN 'Replication Snapshot'

        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'

        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'

        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'

        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'

        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'

        ELSE sJSTP.subsystem

      END AS [StepType]

    , [sPROX].[name] AS [RunAs]

    , [sJSTP].[database_name] AS [Database]

    , [sJSTP].[command] AS [ExecutableCommand]

    , CASE [sJSTP].[on_success_action]

        WHEN 1 THEN 'Quit the job reporting success'

        WHEN 2 THEN 'Quit the job reporting failure'

        WHEN 3 THEN 'Go to the next step'

        WHEN 4 THEN 'Go to Step: '

                    + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))

                    + ' '

                    + [sOSSTP].[step_name]

      END AS [OnSuccessAction]

    , [sJSTP].[retry_attempts] AS [RetryAttempts]

    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]

    , CASE [sJSTP].[on_fail_action]

        WHEN 1 THEN 'Quit the job reporting success'

        WHEN 2 THEN 'Quit the job reporting failure'

        WHEN 3 THEN 'Go to the next step'

        WHEN 4 THEN 'Go to Step: '

                    + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))

                    + ' '

                    + [sOFSTP].[step_name]

      END AS [OnFailureAction]

FROM

    [msdb].[dbo].[sysjobsteps] AS [sJSTP]

    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]

        ON [sJSTP].[job_id] = [sJOB].[job_id]

    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]

        ON [sJSTP].[job_id] = [sOSSTP].[job_id]

        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]

    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]

        ON [sJSTP].[job_id] = [sOFSTP].[job_id]

        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]

    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]

        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]

where [sJOB].[name] = 'DAILY_'

ORDER BY [JobName], [StepNo]