How to Find out which SQL Server Agent Running Jobs

Find out which SQL Server Agent Running Jobs

Using T-SQL Script:

SELECT

    J.Name, J.job_ID

 ,A.run_requested_Date

FROM

    msdb.dbo.sysjobs J

        INNER JOIN msdb.dbo.sysjobactivity A

        ON (j.job_id = A.job_id)

WHERE

    run_Requested_date is not null

    AND stop_execution_date is nul

 

A new session is formed and the sysjobactivity table in the msdb database is filled with all the defined jobs that are currently running when the Microsoft SQL Server Agent service is started. This table lists the status and current activity of jobs. To view the status of jobs right now, use SQL Server Agent's Job Activity Monitor. The sysjobactivity table can be used to determine which jobs were being carried out at the time the SQL Server Agent service suddenly ended.

To view job activity

Using SQL Server Management Studio(SSMS)

1.     In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

2.     Expand SQL Server Agent.

3.     Right-click Job Activity Monitor and click View Job Activity.

4.     In the Job Activity Monitor, you can view details about each job that is defined for this server.

5.     Right-click a job to start it, stop it, enable or disable it, refresh its status as displayed in the Job Activity Monitor, delete it, or view its history or properties. To start, stop, enable or disable, or refresh multiple jobs, select multiple rows in the Job Activity Monitor, and right-click your selection.

6.     To update the Job Activity Monitor, click Refresh. To view fewer rows, click Filter and enter filter parameters

Using T-SQL Script

1.     In Object Explorer, connect to an instance of Database Engine.

2.     On the Standard bar, click New Query.

3.     Copy and paste the following example into the query window and click Execute.

 

-- lists activity for all jobs that the current user has permission to view. 

USE msdb ; 

GO 

 

EXEC dbo.sp_help_jobactivity ; 

GO