SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Determine what step of a job is running?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bill_C
Constraint Violating Yak Guru

United Kingdom
291 Posts

Posted - 10/15/2012 :  09:05:19  Show Profile  Reply with Quote
Is there a way to find out (in code!) what the current running step of a job is?

lionofdezert
Aged Yak Warrior

Pakistan
885 Posts

Posted - 10/15/2012 :  10:21:25  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
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/
Go to Top of Page

Bill_C
Constraint Violating Yak Guru

United Kingdom
291 Posts

Posted - 10/16/2012 :  00:38:15  Show Profile  Reply with Quote
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.

Edited by - Bill_C on 10/16/2012 02:37:49
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/16/2012 :  09:35:44  Show Profile  Reply with Quote
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/

Go to Top of Page

Bill_C
Constraint Violating Yak Guru

United Kingdom
291 Posts

Posted - 10/17/2012 :  08:10:59  Show Profile  Reply with Quote
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.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/17/2012 :  23:22:33  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000