Determine when currently executed job started

Question

Post reply

Determine when currently executed job started

imarchenko-236376

SSCommitted

Points: 1734
More actions
August 2, 2005 at 3:04 pm

#88415

Hello!
I am trying to determine whether currently executed job is taking longer than usual. I was wondering whether there is a way to determine start time of currently executed job. I can use sp_help_job or xp_sqlagent_enum_jobs but none of them reports this information.
Any advice is greately appreciated,
Igor

Viewing 13 posts - 1 through 12 (of 12 total)

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

VD Ten Centuries Points: 1149 More actions · Answer 1

See if msdb..sysjobhistory table will give you want you need.

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 2

Veronika,

Unfortunately, sysjobhistory doesn't store data for currently executed jobs.

Igor

VD Ten Centuries Points: 1149 More actions · Answer 3

My next thought is to look at the active processes to see if the job is still running.

Or you can create some type of a job status table. But in this case you will need to add steps to your job to records begin time and end time. This is what I have done in the past.

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 4

Veronika,

I have already implemented solution based on info in sysprocesses table. I am trying to find when specific job started by matching program_name in sysprocesses and job_id (job_id is a part of program_name).

I personally like this approach better since there is no need to add extra steps to the jobs.

Thanks,

Igor

philcart SSC-Forever Points: 47794 More actions · Answer 5

There is an extended stored procedure that is called to return the currently running jobs. I think it's xp_enum_sqlagentjobs, not sure about that though and I don't have access to a server at the moment.

From this procedure you should be able to get the currently executing jobs.

--------------------
Colt 45 - the original point and click interface

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 6

I think yiou are referring to xp_sqlagent_enum_jobs. Unfortunately, this stored procedure doesn't return start time of currently running jobs.

Igor

philcart SSC-Forever Points: 47794 More actions · Answer 7

Yes, but couldn't you link the information you've already got from sysprocesses to the information from the xp?

--------------------
Colt 45 - the original point and click interface

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 8

I am already linking but this is a little bit phuzzy because I am trying to locate process where program_name containg job_id of the job in question.

I was hoping SQL server stores job start time of currently running job somewhere.

philcart SSC-Forever Points: 47794 More actions · Answer 9

I think it stores it in memory somewhere, unless it calculates it based on the job duration prior to writing to sysjobhistory.

Job execution status has always been one of those sticky things to do outside of the GUI.

--------------------
Colt 45 - the original point and click interface

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 10

imarchenko-236376

SSCommitted

Points: 1734

August 3, 2005 at 1:05 pm

#579325

Thanks Phill!

Igor

Carl Federl One Orange Chip Points: 25384 More actions · Answer 11

Gregory Larsen has some SQL which you could adapt in an article titled "Identifying Long Running SQL Server Agent Jobs" at

http://www.databasejournal.com/features/mssql/article.php/3500276

SQL = Scarcely Qualifies as a Language

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 12

I am using his logic but I am not sure whether it is 100% reliable (matching entry in sysprocesses with job_id).

Thanks,

Igor