Stored Procedure to Get SQL Job Execution Status

Introduction

Ever need a programmatic way to figure out if a SQL Agent job is running? I did. After spending a while searching on Google I did not turn up anything I liked so I turned to Profiler and started looking at what calls SQL Management Studio uses. After a bit of poking around I was able to come up with a stored procedure that I could call.

The Meat

The stored procedure is pretty straightforward. Get the job and owner from the system tables, declare a temporary table, call xp_sqlagent_enum_jobs with the results going into the temporary table, finally placing a return value in an output variable and optionally producing a recordset.
Comments at the top of the stored procedure outline the various statuses as of the time of writing. My general rule is everything except a status of 4 means running.

Here is the code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
USE [master]
GO

CREATE PROCEDURE [dbo].[sp_dba_GetSqlJobExecutionStatus](@job_name sysname, @select_data int =0, @execution_status int = NULL OUTPUT)

AS

SET NOCOUNT ON

/*
Is the execution status for the jobs.
Value Description
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions

*/


DECLARE @job_id uniqueidentifier,
@is_sysadmin int,
@job_owner sysname

SELECT @job_id = jv.job_id,
@job_owner = sp.name
FROM msdb.dbo.sysjobs_view jv
INNER JOIN sys.server_principals sp
ON jv.owner_sid = sp.sid
WHERE jv.name = @job_name

SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)

CREATE TABLE #xp_results
(
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, -- BOOL
request_source int NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running int NOT NULL, -- BOOL
current_step int NOT NULL,
current_retry_attempt int NOT NULL,
job_state int NOT NULL
)

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id

SELECT @execution_status = job_state
FROM #xp_results

DROP TABLE #xp_results

IF @select_data =1
SELECT @job_name AS job_name,
@execution_status AS execution_status

SET NOCOUNT OFF
GO

EXEC sp_MS_marksystemobject 'sp_dba_GetSqlJobExecutionStatus'

Conclusion

I hope you find this stored procedure as useful as I do. Please let me know if you run into any issues or have any ideas that would make it better. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

6 thoughts on “Stored Procedure to Get SQL Job Execution Status

  1. Note that is is possible to have functioning Agent Jobs whose owner is not present in the Logins branch. The inner join to sys.server_principals would cause the SP to fail. I know of no generic way to fix this, however – except to build a xp wrapper for the OS’ security apis to do the sid-name translation.

    1. I have not run into that one yet. I should probably add a script to fully remove a login to my list. I cannot count the times that I have deleted a login and forgot to check if they own any databases. I have been wanting to work on one to fully script out a user with login, server roles / permissions, database roles / permissions so maybe I will tackle that one at the same time.

  2. This doesn’t seem to work for me. The execution status is always 4 – even while the job is running.

    It seems my sql agent user’s SID isn’t in the sys.server_principals table for some reason. No idea why.

Leave a Reply

Your email address will not be published. Required fields are marked *

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