posted 4/19/2011 by kylewalker - Views: [5769]
Usually I decide what to blog about by running across something cool or challenging during work. But every once in a while I like to share something that I've received a number of questions about in a short period of time. Ya never know who else out there has the same question and might not have someone around to ask. A couple weeks ago, on two separate occasions, I was asked how you can get just a simple list of all the jobs running on your server. I know a lot of times it's easy to right click on the job in SQL Server Agent in SSMS, but that only works when you have a job with multiple steps. So what about when you just have a one-step job that takes an hour or so to finish, or maybe even a multiple step job, but the first step is taking a lot of time? Well, there's a system stored procedure built in that can give you exactly what you want.
msdb..sp_help_job @execution_status = 1
"@execution_status = 1" is what gives you your jobs that are currently executing. There are other options if you'd like to check on the status of your other jobs, and they are:
0 = Non-idle
2 = Waiting for thread
3 = Between retries
4 = Idle
5 = Suspended
7 = Performing completion actions
(Yes... They skipped 6... 6 will only give you an error)
For more information on different things you can do with this system stored proc, follow this link to MSDN and check it out!