If you landed on this post, I’m guessing you know the answer to this, so I’ll be quick.
xp_sqlagent_enum_jobs is an undocumented (and thus, unsupported) extended procedure that gives information on whether a job is currently running, when it last ran, when it runs next, etc.
The supported alternative is to use
sp_help_job (which calls
sp_help_job is difficult to use in automation because trying to do
INSERT #temp EXEC sp_help_job; will result in an error due to nested
INSERT...EXEC statements (Error 8164: “An INSERT EXEC statement cannot be nested.”). As a result, people often turn to calling the unsupported xp directly.
A while back, I was working on a server migration to move an (ancient) instance to new hardware. As I looked at all the server-level stuff that needed to be moved, I saw some application accounts had more permissions than I expected (they were sysadmin). I dug in more and realized there was some application code making calls to
Fast forward past the “why are you even doing this?” conversation, and I was looking for a way to allow the application to look at SQL Agent job status without calling
xp_sqlagent_enum_jobs. I don’t mind using unsupported objects when it’s necessary, but I avoid it when possible. I also like to avoid granting applications anything more than
SELECT from system databases. In particular, we don’t REALLY know what this procedure does, and it’s subject to change at any time because it’s undocumented–it probably won’t change, but it could…and that’s a risk.
The developer wasn’t using everything from the output of
xp_sqlagent_enum_jobs. The application was just checking a handful of attributes:
- Is the job running right now?
- What was the last job step to run?
- If the job is not running
- What was the last run status?
- When did it last run?
- When will it run next?
I’m not shy about backwards-engineering these things, and I had a specific use case. I rolled up my sleeves and took a look at
msdb. I found most of the information I wanted lived in
msdb.dbo.sysjobactivity. Soon after finding that table, I had my joins sorted out, and had a
SELECT statement that seemed to work. The best part is that all of these tables are documented and supported:
SELECT * FROM msdb.dbo.sysjobs j LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = j.job_id AND ja.run_requested_date IS NOT NULL AND ja.start_execution_date IS NOT NULL LEFT JOIN msdb.dbo.sysjobsteps js ON js.job_id = ja.job_id AND js.step_id = ja.last_executed_step_id LEFT JOIN msdb.dbo.sysjobhistory jh ON jh.job_id = j.job_id AND jh.instance_id = ja.job_history_id;
This is actually pretty close to what I needed, except for a few things.
SELECT *wasn’t going to work for production
- It didn’t explicitly tell me if the job is currently running
- The run_status column is just a number 0-3
It seemed like these things would be easy enough to sort out. I was well on my way.
Problem 1) Is the job running?
I realized it was incredibly easy to infer if the job was running just by looking at the start & end dates: If the end date is null, then the job is still running. I know of one scenario where this logic fails. If the last execution of the job ended when SQL Agent crashed, then it will look like the job is still running. SQL Agent crashes infrequently enough that I didn’t worry about this.
Problem 2) decoding the run_status column
Google to the rescue. The Books Online page for dbo.sysjobhistory spells out what each of those codes mean. Using documented objects was already coming in handy. In my code, I added the status of “Running” in addition to the documented options.
Creating a TVF
I decided that for this scenario, we’d use a Table-Valued Function in the application’s database, referencing msdb. This fit the developer’s need best, since they could pass in the job name and get a result set back, all without needing an extra
CREATE FUNCTION dbo.AgentJob_Status (@JobName sysname) RETURNS TABLE AS RETURN SELECT TOP 1 JobName = j.name, IsRunning = CASE WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL THEN 1 ELSE 0 END, RequestSource = ja.run_requested_source, LastRunTime = ja.start_execution_date, NextRunTime = ja.next_scheduled_run_date, LastJobStep = js.step_name, RetryAttempt = jh.retries_attempted, JobLastOutcome = CASE WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL THEN 'Running' WHEN jh.run_status = 0 THEN 'Failed' WHEN jh.run_status = 1 THEN 'Succeeded' WHEN jh.run_status = 2 THEN 'Retry' WHEN jh.run_status = 3 THEN 'Cancelled' END FROM msdb.dbo.sysjobs j LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = j.job_id AND ja.run_requested_date IS NOT NULL AND ja.start_execution_date IS NOT NULL LEFT JOIN msdb.dbo.sysjobsteps js ON js.job_id = ja.job_id AND js.step_id = ja.last_executed_step_id LEFT JOIN msdb.dbo.sysjobhistory jh ON jh.job_id = j.job_id AND jh.instance_id = ja.job_history_id WHERE j.name = @JobName ORDER BY ja.start_execution_date DESC; GO
Because it’s a TVF, you can pass a value directly to it and get a result set back:
SELECT * FROM dbo.AgentJob_Status ('Test Job')
Or APPLY it across a table for a set of results (in this case I’m getting all job in job_category 10):
SELECT sts.* FROM msdb.dbo.sysjobs j CROSS APPLY dbo.AgentJob_Status (j.name) sts WHERE j.category_id = 10;
Or get a list of all SQL Agent jobs that are currently running:
SELECT sts.* FROM msdb.dbo.sysjobs j CROSS APPLY dbo.AgentJob_Status (j.name) sts WHERE sts.IsRunning = 1
It’s not quite the same
My TVF isn’t exactly the same as
xp_sqlagent_enum_jobs. Column names are different, there’s a different number of columns, I present some data different, and there’s just some data that isn’t there:
- I give the last completed step instead of the current step–Adding the current step is possible based on the job definition, its just extra work that I didn’t need to do
- I don’t include the “Next Run Schedule ID”, “Requested To Run”, or “Request Source ID” columns
There’s a good chance that if you’re using
xp_sqlagent_enum_jobs, you don’t have to. You should consider using the documented objects in
msdb and querying the tables directly. This way, you can grant SELECT on just four tables in MSDB instead of granting EXECUTE on an undocumented stored procedure in master. In my opinion, you can make your code much more intuitive and easier to read compared to