Getting Running SQL Agent Jobs

Andy Mallon introduces us to xp_sqlagent_enum_jobs, tells us not to use it, and gives us an alternative:

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. As a result, people often turn to calling the unsupported xp directly.

I never actually knew about this procedure; I always just wrote queries against the msdb tables.  Andy’s solution is more elegant than what I normally come up with, though.

Related Posts

When Disabled SQL Agent Jobs Still Run

David Fowler troubleshoots a nasty issue with the SQL Server Agent: In a previous blog post: Duplicate Agent jobs – A good reason not to meddle with Msdb I explained a situation where someone was updating msdb tables manually rather than using the supplied system stored procedures such as msdb.dbo.sp_update_job, It would seem that this was not […]

Read More

Creating Timelines With dbatools

Marcin Gminski shows how to pull SQL Agent and backup history out of SQL Server and display it as a visual history timeline: Currently, the output from the following commands is supported: Get-DbaAgentJobHistory Get-DbaBackupHistory You will run the above commands as you would normally do but pipe the output to ConvertTo-DbaTimeline, the same way as you […]

Read More


February 2016
« Jan Mar »