SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create Alert/Report If Job Duration > Value


Create Alert/Report If Job Duration > Value

Author
Message
David Hart-382918
David Hart-382918
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 187
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.
David Hart-382918
David Hart-382918
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 187
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_ID UNIQUEIDENTIFIER,
Last_Run_Date INT,
Last_Run_Time INT,
Next_Run_Date INT,
Next_Run_Time INT,
Next_Run_Schedule_ID INT,
Requested_To_Run INT,
Request_Source INT,
Request_Source_ID VARCHAR(100),
Running INT,
Current_Step INT,
Current_Retry_Attempt INT,
State INT

)

-- 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'
Exec sp_stop_job @Job_Id = @JobID
END
ELSE
BEGIN
-- Job is not running
END

DROP TABLE #JobStatus
Jonathan Kehayias
Jonathan Kehayias
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11661 Visits: 1819
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

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search