1

I have been working on a query to show me the running jobs. I got it right.

the script is below (enjoy):

DECLARE @JOB_NAME SYSNAME=NULL SELECT job.name ,job.job_id ,StepName=CASE WHEN ja.Last_Executed_Step_ID IS NULL THEN js.Step_Name ELSE js2.Step_Name END ,job.originating_server ,ja.run_requested_date ,[Step Executing]=CASE WHEN ja.last_executed_step_id IS NULL THEN 'Step 1 executing' ELSE 'Step ' + convert(varchar(20), last_executed_step_id + 1) + ' executing' END ,[Running For] = CASE WHEN RADHE.YEARS > 0 THEN CAST( RADHE.YEARS AS VARCHAR(20) ) + ' Anni ' ELSE '' END + CASE WHEN RADHE.MONTHS> 0 THEN CAST( RADHE.MONTHS AS VARCHAR(20) ) + ' Mesi ' ELSE '' END + CASE WHEN RADHE.WEEKS> 0 THEN CAST( RADHE.WEEKS AS VARCHAR(20) ) + ' Settimane ' ELSE '' END + CASE WHEN RADHE.DAYS> 0 THEN CAST( RADHE.DAYS AS VARCHAR(20) ) + ' Giorni ' ELSE '' END + CASE WHEN RADHE.HOURS> 0 THEN CAST( RADHE.HOURS AS VARCHAR(20) ) + ' Ore ' ELSE '' END + CASE WHEN RADHE.MINUTES> 0 THEN CAST( RADHE.MINUTES AS VARCHAR(20) ) + ' Minuti ' ELSE '' END + CASE WHEN RADHE.SECONDS> 0 THEN CAST( RADHE.SECONDS AS VARCHAR(20) ) + ' Secondi ' ELSE '' END FROM msdb.dbo.sysjobs_view job WITH(NOLOCK) INNER JOIN msdb.dbo.sysjobactivity ja WITH(NOLOCK) ON job.job_id = ja.job_id INNER JOIN msdb.dbo.syssessions sess WITH(NOLOCK) ON sess.session_id = ja.session_id INNER JOIN ( SELECT MAX(agent_start_date) AS max_agent_start_date FROM msdb.dbo.syssessions WITH(NOLOCK) ) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date LEFT OUTER JOIN msdb.dbo.SysJobSteps js WITH (nolock) ON (job.Job_ID = js.Job_ID AND ISNULL(ja.Last_Executed_Step_ID, job.Start_Step_ID) = js.Step_ID) LEFT OUTER JOIN msdb.dbo.SysJobSteps js2 WITH (nolock) ON (js.Job_ID = js2.Job_ID AND (js.On_Success_Step_ID = js2.Step_ID OR (js.On_Success_Action = 3 AND js.Step_ID + 1 = js2.Step_ID))) CROSS APPLY( SELECT FLOOR ( UpTime / 31207680 ) AS YEARS , FLOOR( (UpTime / 2600640 ) - FLOOR ( UpTime / 31207680 ) * 12 ) AS MONTHS , FLOOR( (UpTime / 604800 ) - FLOOR ( UpTime / 2600640 ) * 4.3 ) AS WEEKS , FLOOR( (UpTime / 86400 ) - FLOOR( UpTime / 604800 ) * 7 ) AS DAYS , FLOOR( ( UpTime / 3600 ) - FLOOR( UpTime / 86400 ) * 24 ) AS HOURS , FLOOR( ( UpTime / 60 ) - FLOOR( UpTime / 3600 ) * 60 ) AS MINUTES , UpTime - FLOOR( UpTime / 60 ) * 60 AS SECONDS FROM ( SELECT DATEDIFF(SECOND, ja.run_requested_date, GETDATE()) AS UpTime FROM msdb.dbo.sysjobactivity Govinda WITH(NOLOCK) WHERE Govinda.job_id = ja.job_id AND Govinda.session_id = sess.session_id ) AS RadheX ) Radhe WHERE run_requested_date IS NOT NULL AND stop_execution_date IS NULL AND ((@JOB_NAME IS NULL) OR (job.name LIKE @JOB_NAME))

this is the result I get: enter image description here

That is all correct as my current environment and time, however, the duration is of the entire job.

this one hour and something refers to how long the job has been running, not only this particular step that shrinks the database.

How can I find out, the total job duration (as it is) and the duration specifically for the step that is currently running?

Improve this question
3
  • 2
    Take a look at msdb.dbo.sysjobhistory which stores the step and runtime for each step in the job. There is status column (in progress, succeeded, failed, etc.) and a duration column. sysjobhistory Commented Aug 8, 2024 at 21:45
  • @MarcelloMiorelli have you figured out how to view Steps, total duration of Job and duration of steps specifically? if so, could you post the updated query for viewing the jobs along with steps ? Commented Aug 21, 2024 at 19:17
  • @AlekseyVitsko it is on my list, but other things have taken priority, apologies pal Commented Aug 21, 2024 at 20:06

2 Answers 2

Reset to default
0

I wrote the following query to list whole jobs duration and dates, times.

declare @job_id uniqueidentifier ='52B11298-F14A-436E-9313-2DE54B78B37F' select j.name Job_Name, h.run_date [Date], h.run_time [Time], h.run_duration [Duration (sec)], convert(varchar(8), dateadd(second, run_duration, 0), 108) [Duration (hh:mm:ss)] from msdb.dbo.sysjobhistory h left join msdb.dbo.sysjobs j on h.job_id=j.job_id where j.job_id=@job_id and step_id=0 union all select Job_Name,Run_Date,[Time],[Duration (sec)],[Duration (hh:mm:ss)] from ( select top 1 j.name Job_Name, convert(int,convert(nvarchar(10),start_execution_date,112)) [Run_Date], convert(int,replace(convert(varchar(8), start_execution_date, 108),':','')) [Time], convert(int,replace(convert(varchar(8), getdate()-start_execution_date, 108),':','')) [Duration (sec)], convert(varchar(8), getdate()-start_execution_date, 108) [Duration (hh:mm:ss)] from msdb.dbo.sysjobs j left join msdb.dbo.sysjobactivity a on a.job_id=j.job_id where j.job_id=@job_id and Convert(int,Convert(nvarchar(10),start_execution_date,112))>=(select max(h.run_date) from msdb.dbo.sysjobhistory h where h.job_id=@job_id ) and a.job_history_id is null order by session_id desc ) Tac order by h.run_date,h.run_time

It shows the following result when job is not running.

enter image description here

While the job is running the result is as follows.

enter image description here

When you refresh the query, the job duration proceeds. enter image description here

After the job complation the result is as follows. enter image description here

Improve this answer
0

How can I find out, the total job duration (as it is) and the duration specifically for the step that is currently running?

Recently I had to troubleshoot jobs with similar needs, so I created ViewRunningJobs procedure.
It can be downloaded from Github Repo.

It shows job's total duration (leftmost column):

Total Job Duration

And duration of the currently executed step (rightmost column).

Current Step Duration

The procedure's script also contains code for test job with 3 steps, each 1 minute duration. This is commented section at the bottom. It helped to test the SP and verify it shows correct results.

ViewRunningJobs procedure has optional parameter @Detailed, when set to 1, it will show more columns regarding the job/step.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.