Determine what step of a job is running? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Determine what step of a job is running?

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2012-10-15 : 09:05:19

Is there a way to find out (in code!) what the current running step of a job is?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-10-15 : 10:21:25

SELECT J.name,JA.last_executed_step_id,JA.last_executed_step_date
FROM MSDB.dbo.sysjobactivity JA
INNER JOIN MSDB.dbo.sysjobs J ON JA.job_id = J.job_id
WHERE stop_execution_date IS NULL
AND J.name = 'YourJobNameHere'
ORDER BY start_execution_date desc

--------------------------
http://connectsql.blogspot.com/

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2012-10-16 : 00:38:15

Thanks, but that code returns NULL for the last_executed_step_id & last_executed_step_date,

I think they only get populated once the step has finished running, what I'm looking for is info on the currently running step, when it started, how long it has been running for.

I need this info so I can stop the job if the currently running step it is taking way over it's normal run-time.

I'll dig a little deeper.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-16 : 09:35:44

quote:
Originally posted by Bill_C

Thanks, but that code returns NULL for the last_executed_step_id & last_executed_step_date,

I think they only get populated once the step has finished running, what I'm looking for is info on the currently running step, when it started, how long it has been running for.

I need this info so I can stop the job if the currently running step it is taking way over it's normal run-time.

I'll dig a little deeper.

we do it by means of control table

the job step has logic to put in a record with startdate on the table and we check time elapsed after a particular threshold and if we find it crossed threshold we add logic to stop the job

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2012-10-17 : 08:10:59

quote:
Originally posted by visakh16

quote:
Originally posted by Bill_C

Thanks, but that code returns NULL for the last_executed_step_id & last_executed_step_date,

I think they only get populated once the step has finished running, what I'm looking for is info on the currently running step, when it started, how long it has been running for.

I need this info so I can stop the job if the currently running step it is taking way over it's normal run-time.

I'll dig a little deeper.

we do it by means of control table

the job step has logic to put in a record with startdate on the table and we check time elapsed after a particular threshold and if we find it crossed threshold we add logic to stop the job

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks, I'll give that a shot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-17 : 23:22:33

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources