May 24, 2004 at 2:40 pm
I'm trying to write a process that will determine if there are any jobs that have run longer that 12 hours. Now I can determine all the jobs running by issuing the following command:
sp_help_job @execution_status=0
But what I am having a hard time doing is finding an easy way to determine when the jobs identified by the above command actually started. I have found a kludge-of-a-way by converting the job_id into a character representation and then searching the sysprocesses table looking at records where the program_name is like the job_id.
I'm guessing someone on the forum must have an easier way to identify the start time of an existing running job, programatically.
Gregory A. Larsen, MVP
May 24, 2004 at 5:18 pm
May 24, 2004 at 8:17 pm
I've looked at all the MSDB tables, and don't seem to see any table that tracks running jobs. Of course there is a sysjobhistory table but information does not appear in here until at least one step has completed. So basically if you have a long running single step job you can't find any information in this table.
Now I could have easily overlooked something, so if you can give me a little more specific information regarding which table you think might contain information on currently running jobs then that would be great.
Gregory A. Larsen, MVP
May 24, 2004 at 8:58 pm
Greg,
The sysjobhistory Table is the one however I see now what you are trying to do! Thats right this table is history only and until a step has completed you won't see info in there.
I would find out what the lobg running jobs are by doing an sp_who. With this you should be able to find out which jobs are running longer than normal. failing that, try using SQL Profiler for more detailed information.
May 25, 2004 at 10:48 am
Please explain more. I'm not quite sure how you get from sp_who to determining how long a job is running.
Gregory A. Larsen, MVP
July 8, 2004 at 5:19 pm
Greg, I just had a similar need and ran across a post by Louis See1 that referenced sp_get_composite_job_info (which is called by sp_help_job).
I modified this sp slightly by adding a where clause to the final select to get jobs that are not idle and added a parameter to check to see if a specific job is running. (Use with caution: comments in the sp say it is only intended to be called by sp_help_job)
I then put this in a job that runs after the time when the job to be monitored should have ended. This could be done with multiple jobs. In my case I really just needed to know if one job was still running at a particular time and page me if it is running.
However, I contemplated expanding this to track any long running jobs by inserting this data into a table that could be checked periodically.
Here's the concept:
You would need to add a datetime field to the final select statement in the sp, using getdate() at time of insert. This isn't the start time, but a baseline time to compare against later.
Within sp, modify the final select statement to insert into a tmpJobs_table as well as insert to a jobs_table where not in (select name from jobs_table). Then delete from jobs_table where not in tmpjobs_table. So, each time you run the sp, any running jobs not already in the jobs_table are inserted and any job no longer running is deleted.
Set up a job that runs every half hour (or whatever is appropriate) with these steps:
1. exec sp_get_composite_job_info_MODIFIED -- which updates the running jobs_table
2. if exists (SELECT * FROM jobs_table where datediff(hh,datetime_field, getdate())>12 ) raiserror('Job running too long.',16,1)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply