start time of a job while it is running

Question

start time of a job while it is running

andrewkane17

SSCertifiable

Points: 7529
More actions
August 24, 2005 at 10:29 am

#87330

I posted this on the admin forum yesterday and did not get much response, I know I am cross posting and I appologize but does any one have any clues of how to find the start time of a job while it is running, not after it has finished execution.
I have tried to implement the long running jobs script as supplied by Gregory A. Larsen at http://www.databasejournal.com/features/mssql/article.php/3500276 and I thank him for the script.
The process relies on the job_id of the executing job to be present in the sysprocess table in hex format. The script joins on this table to give you the login time of a running job and thus the time the job has been executing.
My problem is that if the job has an operating system command as the only step (dts etc) then the program called by the job is populated in sysprocess table (DTS Designer for example). Does any one have/know any way that I can get the start time of a running job even if the job step is an operating system command.
What I want to do is poll my servers and write to a table, job name, start time run duration. I can get the state of the job using xp_sqlagent_enum_jobs, but I need the time a currently executing job began its execution.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

The Notification Services Admin forum & SQL Admin forum often get mixed up. It's a little confusing the way it's laid out.

We recently bought Idera's Diagnostic Manager, and it has the type of alert you want.

I bet there's a way to do it yourself though.

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 2

After banging my head against the wall I have the good luck to look at this post http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=213518 from bnye.home.att.net who gives me the last bit of the puzzle.

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

homebrew01

SSC Guru

Points: 55548

August 25, 2005 at 11:02 am

#585098

Your post just links to this same thread.

Bill Nye Hall of Fame Points: 3248 More actions · Answer 4

Happened to run across this and since my old web site is no longer active, here is the link to the post that would have been on my web site that referred back to http://www.sqlservercentral.com/Forums/Topic117401-5-1.aspx#bm125482.

Bill

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

For jobs where I am likely to need to know this information, I add a step at the beginning called Initialise. I set the subsystem to CmdExec so that it doesn't make a connection to the database, and I set the command to something like "REM Do nothing". That way, you always know when your job started, whether it's finished or not.

John