Does anybody have a way of monitoring for long running sql agent jobs? I've looked at all the usual suspects, sp_help_job, xp_sqlagent_enum_jobs, job tables etc but none of them seem to tell me how long a job has been running or even when it started (it must be held somewhere mustn't it?)
We had a situation were a sql agent job (calling a dts package that executes a batch file) had been running for days waiting for a response from a RSH to a mainframe). Nobody knew because it didn't fail.
I know I can set the timeout for a batch file execution from within the DTS package, but I would prefer to monitor the execution of the Agent job.
You'll need a bit of T-SQL coding
First you'll need to record the previous run duration. Maybe store an average value for past executions.Record when a job startsThen use xp_sqlagent_enum_jobs to determine which jobs are running.For the jobs that are running, compare the datediff from the start time against your stored duration.
Have a browse throught the script library and you might find something useful there.
My problem is that I can't seem to find where the job start time is stored. Any ideas?
I use this script to capture info on job/job step duration
DECLARE @JOBNAME varchar(200)
SET @JOBNAME = 'YoUR Job Name'
SELECT DISTINCT j.name [JOB], jt.Step_ID [StepID],jt.Step_Name [Step], SUBSTRING(CAST(jt.Last_Run_Duration AS varchar(10)) ,LEN(CAST(jt.Last_Run_Duration AS varchar(10)))-5,2) [Hours], SUBSTRING(CAST(jt.Last_Run_Duration AS varchar(10)) ,LEN(CAST(jt.Last_Run_Duration AS varchar(10)))-3,2) [Minutes], RIGHT(jt.Last_Run_Duration,2)[Seconds],jt.Last_Run_Date [Last Run Date],jt.Last_Run_Time [Time],GetDate() [Check Date]FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobschedules js ON js.job_Id = j.job_id INNER JOIN msdb.dbo.sysjobsteps jt ON jt.job_id = j.job_idWHERE j.name = @JobNameORDER BY [StepID]
Enter the name of your job in the job name variable and run this against the MSDB databse.
I tried this but it only gives me info on the last run of the job, not how long the job has been running if it is currently running.
I've found a solution here http://www.sqldts.com/default.aspx?271
Thanks for your thoughts and replies.