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.
- is sysadmin or not: value can be 0 or 1
- Job Owner name: string value, no default, not null. The value of it is ignored when the value of the first parameter is 1
- 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.
Hi John,
This is a great post. You documented an undocumented SP in a great detail. Thanks for your great work! — Steven
Perfect, thanks.
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.
If the backup script is created by yourself, you can definitely put some information in the context info
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.
Awesome, thanks very much for some great help!
Great post. Huge help. Thanks! I particularly liked the sys.dm_exec_sessions view.
Excellent post! Much appreciated.
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?
Yes, I saw that few times in my life as well. That might be just a bug in SQL agent