xp_sqlagent_enum_jobs alternative

What’s xp_sqlagent_enum_jobs?

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 xp_sqlagent_enum_jobs), but 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.

The backstory

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 master.dbo.xp_sqlagent_enum_jobs.

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 requirements

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?

The hack

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.

The solution

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 INSERT...EXEC.

You can grab the latest version of dbo.AgentJob_Status() on GitHub as part of my open-source DBA Database.

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 xp_sqlagent_enum_jobs.