http://www.sqlservercentral.com/blogs/adventuresinsql/2010/01/31/stored-procedure-to-get-sql-job-execution-status/

Printed 2014/12/18 12:39AM

Stored Procedure to Get SQL Job Execution Status

By David Levy, 2010/01/31

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.