Skip to main content

Script to get running schedule jobs - SQL Server

A few days back, while I was working with Alerts for failed scheduled jobs and missing scheduled jobs, I was needed to exclude running jobs. So we can exactly know which jobs are actually failed excluding running jobs and sane case for missing jobs. I applied some additional in the existing script to fetch the records of failed scheduled jobs or missing jobs. Let me share a script here to get all running scheduled jobs,
USE msdb

CREATE TABLE #RunningJobs 
    job_id                UNIQUEIDENTIFIER NOT NULL,   
    last_run_date         INT              NOT NULL,   
    last_run_time         INT              NOT NULL,   
    next_run_date         INT              NOT NULL,   
    next_run_time         INT              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
INSERT INTO  #RunningJobs  
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa'

FROM   #runningjobs rj 
       INNER JOIN dbo.sysjobs sj 
               ON ( sj.job_id = rj.job_id ) 
WHERE  rj.running = 1 

DROP TABLE #RunningJobs
Above script use dbo.xp_sqlagent_enum_jobs undocumented object from the master database. It is also useful to get all other state values of jobs like following which received from forums,
0 = Not idle or suspended,
1 = Executing,
2 = Waiting For Thread,
3 = Between Retries,
4 = Idle,
5 = Suspended,
6 = WaitingForStepToFinish,
7 = PerformingCompletionActions
I am using this script in failed jobs and missing jobs list. You might be using it somewhere, Please share your comments. I will publish further posts to get failed jobs and missing jobs list.