SQL Server Performance Forum – Threads Archive

Job run time

Hi Guys, I have some jobs running on a server that every now and then take a lot longer to run that they are ment to. I am looking to find a way of flagging jobs that have been running for more then say 8 hours. Any one know how I might do this. So far I have come up with the following:- use msdb
select * from sysjobhistory where run_status=4 and run_duration > 10 But do not get the results I would expect. Any ideas? Thanks Dave

---

Did you try run_duration in HHMMSS format? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.

---

As Martin said, you should use : select * from msdb.dbo.sysjobhistory where run_status=4 and run_duration > 80000 — eight hours.

---

You cannot use the sysjobhistory table to check whether a job is running because the sysjobhistory table does not get populated until after the fact. Try this. Get a server with no jobs in it and create a single job. Add a step that does nothing but a "waitfor delay ’00:10:00’". Run the job and then check the sysjobhistory table. Notice that it doesn’t get populated.
What you need to do instead is use the xp_sqlagent_enum_jobs extended proc (this is the only reliable way of getting accurate information about job statuses). You can insert the the results of xp_sqlagent_enum_jobs into a table and check the status value for the corresponding job (if it is not 4 then the job is running.
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, — BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default null,
running INT NOT NULL, — BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) — Capture job execution information (for local jobs only since that’s all SQLServerAgent caches)
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N’sysadmin’), 0)
SELECT @job_owner = suser_sname(suser_sid())
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner –check if the job is running.
IF @JobStatus <> 4
/*
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
*/
–handle running jobs in whatever way you want. Hope that helps you get started.

Karl Grambow www.sqldbcontrol.com

---

]]>