sql server query to show the running jobs - what about the steps of the job?
1
vote
1
answer
120
views
I have been working on a query to show me the running jobs.
I got it right.
the script is below (enjoy):
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?
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:

Asked by Marcello Miorelli
(17274 rep)
Aug 8, 2024, 07:37 PM
Last activity: Aug 9, 2024, 01:52 PM
Last activity: Aug 9, 2024, 01:52 PM