Search This Blog

Friday, October 29, 2010

Script: view information of currently running SQL Server Jobs

Below script returns five (5) columns result set as output. You can further enhance this query to accommodate your requirement.

  1. Job_Name: This is job’s registered name on the server’s instance.
  2. Last_Executed_Step_ID: This is the ID of the last executed step in the job. E.g. if a job has 4 steps, and currently it is running 3rd step, so last executed step will be 2 and it will be populated with 2 against Job’s 2nd step row, while all other steps rows will have 0.
  3. Step_ID: This query returns all steps included in the job along with their id, while 2nd column, returns id same as in this column.
  4. Step_Name: This reflects job step name that was given during job creation.
  5. Start_Execution_Date: This column returns execution date and time of this job. So you can get an idea when this started execution.

,ISNULL(a.last_executed_step_id,'') last_executed_step_id

FROM msdb.dbo.sysjobs j WITH (NOLOCK)
INNER JOIN msdb.dbo.sysjobsteps s WITH (NOLOCK) ON j.job_id = s.job_id
LEFT OUTER JOIN msdb.dbo.sysjobactivity a WITH (NOLOCK) ON j.job_id=a.job_id
AND (s.step_id=a.last_executed_step_id OR a.last_executed_Step_id IS NULL)
AND stop_execution_Date IS NULL

WHERE j.enabled=1
AND j.category_id IN (0,1,2,3,4,5,98,99)
AND j.job_id in 
(SELECT job_id FROM msdb.dbo.sysjobactivity WITH (NOLOCK)
AND stop_execution_date IS NULL )


No comments: