TSQL code to view Job Activity

SQL Server Agent is a very key component of SQL Server and typically used to schedule jobs,alerts etc. Whenever we need to restart SQL Server agent, it is good practice to make sure there are no running jobs. We can check this using job activity monitor or using the TSQL code below.

I prefer the TSQL code as it is easier and also can run it on central management server(CMS) to get the list of running jobs across multiple servers.

--currently running jobs in job activity monitor(SQL 2005 and above)
Select @@SERVERNAME as [DBServer],C.Name as [JobName], run_requested_date,start_execution_date,stop_execution_date
from (Select max(Session_id) as Session_Id from msdb.dbo.syssessions) A
INNER JOIN msdb.dbo.sysjobactivity B on A.Session_id=B.Session_ID
INNER JOIN msdb.dbo.sysjobs C on B.job_id=C.Job_ID
where B.stop_execution_date is null AND B.run_requested_date is not null