Monitoring Long Running Agent Jobs

Question

Post reply

Monitoring Long Running Agent Jobs

Green Armchair

Hall of Fame

Points: 3561
More actions
April 14, 2005 at 3:35 am

#109742

Hi
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.
Thanks
Andy

Viewing 9 posts - 1 through 8 (of 8 total)

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

andyobl Right there with Babe Points: 767 More actions · Answer 1

You will need a bespoke script to that I think.

Check out the link below.

http://databasejournal.com/features/mssql/article.php/2168291

philcart SSC-Forever Points: 47796 More actions · Answer 2

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 starts

Then 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.

--------------------
Colt 45 - the original point and click interface

Green Armchair Hall of Fame Points: 3561 More actions · Answer 3

Thanks Phill

My problem is that I can't seem to find where the job start time is stored. Any ideas?

Andy

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 4

Hi Andy,

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_id

WHERE j.name = @JobName

ORDER BY [StepID]

Enter the name of your job in the job name variable and run this against the MSDB databse.

Any thoughts?

Graeme

Green Armchair Hall of Fame Points: 3561 More actions · Answer 5

Thanks Graeme

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.

Andy

Green Armchair Hall of Fame Points: 3561 More actions · Answer 6

I've found a solution here http://www.sqldts.com/default.aspx?271

Thanks for your thoughts and replies.

Andy

fai0626 Mr or Mrs. 500 Points: 522 More actions · Answer 7

Hi,

For SQL 2005 or after, you can get current job execution time from the view

msdb.dbo.sysjobactivity.

PappyBlueRibs99 SSC Rookie Points: 34 More actions · Answer 8

Graeme, I like your suggestion. However, I don't think you need to have "INNER JOIN msdb.dbo.sysjobschedules AS js ON js.job_id = j.job_id" in your statement, since you don't actually use any of the fields. The reason I bring this up is that we have jobs with multiple schedules, and your results get funky if the job has multiple schedules.