Jan 132012
 

Are jobs currently running? This question is asked very frequently by DBAs. The simplest answer is to use SSMS, navigate to SQL Server Agent, double click on Job Activity Monitor under job folder, then check Status column. It is ok if you have one or few servers. Under the situation where you have dozens, hundred, or thousands of SQL Server instances, the simple task described above would become to an extremely challenging work for human beings. The best way is to check them utilizing machine. Here I provide 2 ways to get job execution status programmatically and also tell you how to get the session id of a running job (if it’s running T-SQL Task)


The first approach is to use officially published command msdb.dbo.sp_help_job. You can find the execution status by looking at column current_execution_status in the first set of records returned by this procedure. This procedure returns different sets of records by passing differnt value to @job_aspect parameter. Default value of this parameter is ‘ALL’. When you use default values for all parameter, this procedure returns one set of record. If you specify a Job ID and keep other parameters default, this procedure will return 4 sets of records with 4 different definitions. multiple different sets returned by a procedure cannot be received by any T-SQL objects (temp tables). In order to retrieve job execution status for a specific job, you need to change the default value of @job_aspect to ‘JOB’. For more information, please reference http://msdn.microsoft.com/en-us/library/ms186722.aspx.

There is an undocumeted extended procedure you can utilize to determine the job execution status, master.dbo.xp_sqlagent_enum_jobs. msdb.dbo.sp_help_job internally call this extended procedure to the status of job(s). This procedure has 3 parameters. Name of the parameter can be anything but position of the parameter is important.

  1. is sysadmin or not: value can be 0 or 1
  2. Job Owner name: string value, no default, not null. The value of it is ignored when the value of the first parameter is 1
  3. Job ID, filter on Job ID

Calling this procedure without any parameters or wrong parameters will cause an error returned

exec master.dbo.xp_sqlagent_enum_jobs
Msg 22023, Level 16, State 1, Line 0
Usage:  EXECUTE xp_sqlagent_enum_jobs <is sysadmin (0 or 1)>, <job owner name> [, <job id>]

List all of Jobs

exec master.dbo.xp_sqlagent_enum_jobs 1, ''
Job ID                             Last Run Date Last Run Time Next Run Date Next Run Time Next Run Schedule ID Requested To Run Request Source Request Source ID    Running     Current Step Current Retry Attempt State
---------------------------------- ------------- ------------- ------------- ------------- -------------------- ---------------- -------------- -------------------- ----------- ------------ --------------------- -----------
0x1F3C620FDD7C704A89EEF615E6078B57 20120102      164801        0             0             0                    0                0              NULL                 0           0            0                     4
0x911C96875B220B4D8FB4E3220AA55CFA 20120112      130424        0             0             0                    0                4              SRV88\User12         1           1            0                     1

(2 row(s) affected)

From the returning set, you can check columns Running and Current Step to determine the execution status. You can use following SQL to receive the records returned by this procedure

declare @ExecutionStatus table 
(
	JobID uniqueidentifier primary key, -- Job ID
	LastRunDate int, LastRunTime int, -- Last run date and time
	NextRunDate int, NextRunTime int, -- Next run date and time
	NextRunScheduleID int, -- an internal schedule id
	RequestedToRun int, RequestSource int, RequestSourceID varchar(128),
	running int,  -- 0 or 1, 1 means the job is executing
	CurrentStep int, -- which step is running
	CurrentRetryAttempt int, -- retry attempt
	JobState int ----0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
)

Now let’s see how to link an executing job with a SessionID (SPID) in SQL Server. The information of the linkage is hidden in program_name field in sys.dm_exec_sessions DMV. You can extract the job id from this field. Please see the code below

select a.session_id, b.job_id, b.name
from sys.dm_exec_sessions a
	inner join msdb.dbo.sysjobs b on b.job_id = cast(convert( binary(16), substring(a.program_name , 30, 34), 1) as uniqueidentifier)
where program_name like 'SQLAgent - TSQL JobStep (Job % : Step %)'
/*
session_id job_id                               name
---------- ------------------------------------ ----------
54         87961C91-225B-4D0B-8FB4-E3220AA55CFA test2

(1 row(s) affected)
*/

Now you see the visual association between sessions and job. This can also be the way to check the execution status of jobs but it might be less efficient than calling an extended procedure, however, you can create a view or function on top of this approach. That will bring you flexibilities.

  9 Responses to “Are Jobs Currently Running?”

  1. Hi John,

    This is a great post. You documented an undocumented SP in a great detail. Thanks for your great work! — Steven

  2. Perfect, thanks.

  3. Hi John,
    Not sure if you’ll see this, but this has been a fantastic help on a script I’m trying to put together. I’m trying to check for long running LS_Copy_(DBName) or LS_Restore_(DBName) jobs prior to firing the LS_Alert job as we quite often fire the alert when large TLog files take longer than the out_of_syncThreshold to complete. How would I associate the Job to the database it’s Copying/Restoring. I’ve got this far;
    DECLARE @session_id INT
    DECLARE @job_id UNIQUEIDENTIFIER
    DECLARE @name NVARCHAR(255)

    DECLARE RunningLSJobs CURSOR FOR
    select a.session_id, b.job_id, b.name from sys.dm_exec_sessions a inner join msdb.dbo.sysjobs b on b.job_id = cast(convert( binary(16), substring(a.program_name , 30, 34), 1) as uniqueidentifier)
    where program_name like ‘SQLAgent – TSQL JobStep (Job % : Step %)’
    AND b.name Like ‘LS_Copy%’ OR b.name LIKE ‘LS_Restore%’

    OPEN RunningLSJobs
    FETCH NEXT FROM RunningLSJobs
    INTO @session_id, @job_id, @name
    WHILE @@FETCH_STATUS = 0
    BEGIN

    Select status, command, percent_Complete, minutes_remaining = estimated_completion_Time /1000 /60
    From sys.dm_exec_Requests
    Where session_id = @session_id

    FETCH NEXT FROM RunningLSJobs
    INTO @session_id, @job_id, @name
    END

    CLOSE RunningLSJobs
    DEALLOCATE RunningLSJobs

    I can’t find a table/view that would link them so I’m guessing I’ll have to strip the dbname from the jobname, but just thought I see if you knew another more elegant way.

    Regards,

    Guy

    • Hi Guy,

      there is a database in the dm_exec_sessions view. but it might not help you in this case.

      select a.session_id, b.job_id, b.name , a.program_name, a.database_id
      	from sys.dm_exec_sessions a 
      		inner join msdb.dbo.sysjobs b on b.job_id = cast(convert( binary(16), substring(a.program_name , 30, 34), 1) as uniqueidentifier) 
      	where a.program_name like 'SQLAgent - TSQL JobStep (Job % : Step %)'
      

      If the backup script is created by yourself, you can definitely put some information in the context info

      set context_info 0x123blabla

      then check the context_info field in dm_exec_sessions view. this can give you a *reliably* way to get the info regarding what is running.

  4. Awesome, thanks very much for some great help!

  5. Great post. Huge help. Thanks! I particularly liked the sys.dm_exec_sessions view.

  6. Excellent post! Much appreciated.

  7. I’ve recently encountered an issue where xp_sqlagent_enum_jobs shows that a job is running, when in fact, it isn’t. Any insight on this?

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.