SQL - How to find currently running job steps through TSQL

sql tsql sql-job
user3138788 picture user3138788 · Feb 6, 2014 · Viewed 15.7k times · Source

I am writing a query to find currently running job in SQL (I know we can view it in Job Active Monitor, but I've a need to do in TSQL). Though I can query sysjobactivity table to find currently running job, it's nowhere telling what job step is running (because my job might have more than 1 step).

Query I used:

SELECT s.name AS [JOB_NAME],
       '' AS [STEP_ID], 
       '' AS STEP_NAME, 
       'Processing' AS STATUS, 
       sja.run_requested_date AS START_TIME, 
       null AS END_DATE,
       convert(varchar, (getdate() - sja.run_requested_date), 8) AS Duration 
  FROM sysjobactivity sja, sysjobs s
 WHERE sja.job_id = s.job_id
   AND sja.run_requested_date >  getdate() - 1
   AND sja.stop_execution_date IS NULL

Please help me finding the step ID & Step name in which the job is currently progressing.

Answer

Naveen Kumar picture Naveen Kumar · Feb 6, 2014

I think below script help to get SQL Jobs with current execution step, try this

msdb.dbo.sp_help_job @execution_status = 1

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More