Sample Header Ad - 728x90

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):
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?
Asked by Marcello Miorelli (17274 rep)
Aug 8, 2024, 07:37 PM
Last activity: Aug 9, 2024, 01:52 PM