Create Alert/Report If Job Duration > Value

Question

Create Alert/Report If Job Duration > Value

David Hart-382918

SSChasing Mays

Points: 657
More actions
September 30, 2008 at 9:41 am

#213502

I can view in Log File Viewer | Job History, the duration of a job.
I want to create a report/alert when a particular job/step for a Job Name (Job_id) exceeds a threshold of duration (e.g. Duration > 00.15.00).
I researched msdb and master for a view or stored procedures that I might use as a template for writing the script. I could find no ready tools (reports or dialogue/options to help me.) I am using SQL2005 9.0.2050.
I have had a job hang for > 1 Day, when it usually takes several seconds. No error message was reported. Once, it actually finished; on another occasion, I killed the job, and started it over to success.
Research did not readily point to the problem. So, I'd like to find any job with excessive duration time, and in particular, I want to track this job. It runs every hour/24 hours, and hangs about once a week.

Viewing 3 posts - 1 through 2 (of 2 total)

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

David Hart-382918 SSChasing Mays Points: 657 More actions · Answer 1

How 'bout this as an option? I copied and modified a script found that might allow me to stop a 'hung' job long after it should have completed.

I could add it as a last step to the job with a WAITFOR DELAY. Every time the job runs, it checks for a 'hung' status, and stops it.

All comments and/or modifications welcome.

--OPTION# 1

DECLARE

@JobID UNIQUEIDENTIFIER

--jobid from sysjobs for your job i question'

SET

@JobID = '573878F9-CFB3-417B-B802-7A6B914A2204'

IF OBJECT_ID('tempdb.dbo.#JobStatus') IS NOT NULL

DROP TABLE #JobStatus

CREATE TABLE #JobStatus

(

Job_IDUNIQUEIDENTIFIER,

Last_Run_Date INT,

Last_Run_Time INT,

Next_Run_Date INT,

Next_Run_Time INT,

Next_Run_Schedule_IDINT,

Requested_To_RunINT,

Request_Source INT,

Request_Source_IDVARCHAR(100),

RunningINT,

Current_Step INT,

Current_Retry_AttemptINT,

StateINT

)

-- Retrieve results of last job run

INSERT INTO #JobStatus

EXEC master.dbo.xp_sqlagent_enum_jobs 0,sa,@JobID

--Select * from #JobStatus

-- Check to see if job is running

IF

(

SELECT COUNT(*) FROM #JobStatus

WHERE Running = 1

) > 0

BEGIN

--Do your work here

WAITFOR DELAY '00:05:00'

Execsp_stop_job @Job_Id = @JobID

END

ELSE

BEGIN

-- Job is not running

END

DROP TABLE#JobStatus

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

If the jobs are all SQL based, IE, not calling operating system processes, you can also see them running in sysprocesses:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AgentLongRunning&referringTitle=Home