Monitoring SQL Server agent jobs is a critical task for DBAs. There are several ways of achieving this.
The most obvious method is to use the Job Activity monitor.
For the above list, note that the currently running jobs will have a green arrow icon next to them. However, if you have large number of jobs running this won’t be an easy task so you could use the filtering option of the Job Activity Monitor as shown below.From this dialog you can select to view only jobs which are currently executing, just ensure you select Apply Filter checkbox.
However, some DBAs prefer to use scripts to achieve this, the below TSQL will provide the same detail:
exec msdb..sp_help_job @Execution_status = 1
The below screenshot shows the results obtained by executing the TSQL:
In addition, the sp_help_job will also provide you all the jobs in the server.
]]>