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.
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.
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.
That is a great post!
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.