If you want to find the currently running jobs then following queries will help you.
-
List All Currently Running SQL Agent Jobs:
exec msdb..sp_help_job @execution_status=1
-
List Extra Information about your Job:
exec msdb..sp_help_job @Job_name=<yourjobname>
-
Possible values of @execution_status:
| Value | Description |
|---|---|
| 0 | Returns only those jobs that are not idle or suspended. |
| 1 | Executing. |
| 2 | Waiting for thread. |
| 3 | Between retires. |
| 4 | Idle. |
| 5 | Suspended. |
| 7 | Performing completion actions. |
Links : http://msdn.microsoft.com/en-us/library/ms186722(SQL.105).aspx
