Currently Running SQL Server Agent Jobs

-- =============================================================================
-- Currently Running SQL Server Agent Jobs
-- http://sqlconcept.com/2011/06/25/how-to-query-currently-running-sql-server-agent-jobs/
-- =============================================================================

use msdb;
go

set nocount on;

declare @agentJobs table(
    job_id uniqueidentifier not null,
    last_run_date nvarchar(20) not null,
    last_run_time nvarchar(20) not null,
    next_run_date nvarchar(20) not null,
    next_run_time nvarchar(20) not null,
    next_run_schedule_id int not null,
    requested_to_run int not null,
    request_source int not null,
    request_source_id sysname collate database_default null,
    running int not null,
    current_step int not null,
    current_retry_attempt int not null,
    job_state int not null
);

declare @job_owner sysname;
declare @is_sysadmin int;
declare @current_time datetime;

set @is_sysadmin = ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0);
set @job_owner = SUSER_SNAME();
set @current_time = getdate();

insert into @agentJobs
    execute master.dbo.xp_sqlagent_enum_jobs
        @is_sysadmin,
        @job_owner;

update @agentJobs
    set last_run_time = RIGHT('000000' + last_run_time, 6),
        next_run_time = RIGHT('000000' + next_run_time, 6);

select j.name as JobName,
    j.enabled as Enabled,
    case x.running
        when 1 then 'Running'
        else case h.run_status
                 when 2 then 'Inactive'
                 when 4 then 'Inactive'
                 else 'Completed'
             end
    end as CurrentStatus,
    COALESCE(x.current_step, 0) as CurrentStep,
    case
        when x.last_run_date > 0 then CONVERT(datetime, SUBSTRING(x.last_run_date, 1, 4) + '-' + SUBSTRING(x.last_run_date, 5, 2) + '-' + SUBSTRING(x.last_run_date, 7, 2) + ' ' + SUBSTRING(x.last_run_time, 1, 2) + ':' + SUBSTRING(x.last_run_time, 3, 2) + ':' + SUBSTRING(x.last_run_time, 5, 2) + '.000', 121)
        else null
    end as LastRunTime,
	@current_time as 'CurrentTime',
    case h.run_status
        when 0 then 'Fail'
        when 1 then 'Success'
        when 2 then 'Retry'
        when 3 then 'Cancel'
        when 4 then 'In progress'
    end as LastRunOutcome,
    case
        when h.run_duration > 0 then(h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + h.run_duration % 100
        else null
    end as LastRunDuration
from @agentJobs as x
left join msdb.dbo.sysjobs as j
    on x.job_id = j.job_id
left outer join msdb.dbo.syscategories as c
    on j.category_id = c.category_id
left outer join msdb.dbo.sysjobhistory as h
    on x.job_id = h.job_id
   and x.last_run_date = h.run_date
   and x.last_run_time = h.run_time
   and h.step_id = 0
where x.running = 1;

-- ==========================================
--  SQL Server Agent Job Management Commands
-- ==========================================

use msdb;
go

--
-- To instruct SQL Server Agent to stop the execution of a job.
exec dbo.sp_stop_job N'<job_name>';
go

--
-- To instructs SQL Server Agent to execute a job immediately.
exec dbo.sp_start_job N'<job_name>';
go

--sp_help_job - Returns information about jobs that are used by SQLServerAgent service to perform automated activities in Microsoft SQL Server. 

--Syntax:
--sp_help_job [ [ @job_id = ] job_id ] 
--[ , [ @job_name = ] 'job_name' ] 
--[ , [ @job_aspect = ] 'job_aspect' ] 
--[ , [ @job_type = ] 'job_type' ] 
--[ , [ @owner_login_name = ] 'login_name' ] 
--[ , [ @subsystem = ] 'subsystem' ] 
--[ , [ @category_name = ] 'category' ] 
--[ , [ @enabled = ] enabled ] 
--[ , [ @execution_status = ] status ] 
--[ , [ @date_comparator = ] 'date_comparison' ] 
--[ , [ @date_created = ] date_created ] 
--[ , [ @date_last_modified = ] date_modified ] 
--[ , [ @description = ] 'description_pattern' ]

--
-- To list information for all jobs.
exec dbo.sp_help_job;
go

--
-- To list information on jobs that are enabled and currently running.
exec dbo.sp_help_job @enabled = 1, @execution_status = 1;
go