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

SQL Server Agent Security

Claudio Silva explains how you can provide secure access to manage SQL Agent jobs: It is common having services accounts that are job owners so they can run within the proper context. In my humble opinion, this starts to be a little strange when it comes to modifying the agent job. It means that the […]

Read More

Automated Alert Emails

Max Vernon shows how you can use the SQL Server Agent to send automated e-mails on alerts: SQL Server Agent provides a great mechanism for sending alerts via email in response to various events. Once you’ve setup Database Mail, and configured a SQL Server Agent Operator, you should add alerts for severe errors that affect the […]

Read More

Categories

February 2016
MTWTFSS
« Jan Mar »
1234567
891011121314
15161718192021
22232425262728
29