Are SQL server Jobs Currently Running?



http://www.sqlnotes.info/2012/01/13/are-jobs-currently-running/

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

2	Msg 22023, Level 16, State 1, Line 0

3

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, '' view source print ? 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 2 ---------------------------------- ------------- ------------- ------------- ------------- -------------------- ---------------- -------------- -------------------- ----------- ------------ --------------------- ----------- 3 0x1F3C620FDD7C704A89EEF615E6078B57 20120102      164801        0             0             0                    0                0              NULL                 0           0            0                     4 4 0x911C96875B220B4D8FB4E3220AA55CFA 20120112      130424        0             0             0                    0                4              SRV88\User12         1           1            0                     1 5   6 (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 02 ( 03     JobID uniqueidentifier primary key, -- Job ID 04     LastRunDate int, LastRunTime int, -- Last run date and time 05     NextRunDate int, NextRunTime int, -- Next run date and time 06     NextRunScheduleID int, -- an internal schedule id 07     RequestedToRun int, RequestSource int, RequestSourceID varchar(128), 08     running int,  -- 0 or 1, 1 means the job is executing 09     CurrentStep int, -- which step is running 10     CurrentRetryAttempt int, -- retry attempt 11     JobState int ----0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions 12 ) 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 02 from sys.dm_exec_sessions a 03     inner join msdb.dbo.sysjobs b on b.job_id = cast(convert( binary(16), substring(a.program_name , 30, 34), 1) as uniqueidentifier) 04 where program_name like 'SQLAgent - TSQL JobStep (Job % : Step %)' 05 /* 06 session_id job_id                               name 07 ---------- ------------------------------------ ---------- 08 54         87961C91-225B-4D0B-8FB4-E3220AA55CFA test2 09   10 (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.