August 24, 2005 at 10:29 am
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
August 24, 2005 at 11:30 am
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.
August 24, 2005 at 7:26 pm
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.
August 25, 2005 at 11:02 am
Your post just links to this same thread.
January 7, 2015 at 9:19 am
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
January 7, 2015 at 9:35 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply