Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create Alert/Report If Job Duration > Value Expand / Collapse
Author
Message
Posted Tuesday, September 30, 2008 9:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 17, 2014 9:33 AM
Points: 49, Visits: 182
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.
Post #578413
Posted Tuesday, September 30, 2008 2:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 17, 2014 9:33 AM
Points: 49, Visits: 182
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

Post #578665
Posted Tuesday, September 30, 2008 5:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:10 PM
Points: 1,683, Visits: 1,797
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
Post #578755
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse