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

Monitoring Long Running Agent Jobs Expand / Collapse
Author
Message
Posted Thursday, April 14, 2005 3:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 207, Visits: 304

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




Post #174661
Posted Thursday, April 14, 2005 4:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 2:15 AM
Points: 21, Visits: 101
You will need a bespoke script to that I think.
Check out the link below.

http://databasejournal.com/features/mssql/article.php/2168291
Post #174668
Posted Friday, April 15, 2005 12:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 15, 2014 7:14 PM
Points: 2,693, Visits: 1,223

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.

 



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #174932
Posted Friday, April 15, 2005 1:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 207, Visits: 304

Thanks Phill

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

Andy




Post #174947
Posted Friday, April 15, 2005 2:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 3:37 AM
Points: 1,220, Visits: 700

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

 




Post #174950
Posted Friday, April 15, 2005 4:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 207, Visits: 304

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




Post #174965
Posted Friday, April 15, 2005 7:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 207, Visits: 304

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

Thanks for your thoughts and replies.

Andy




Post #174995
Posted Wednesday, December 30, 2009 12:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 19, 2010 8:20 PM
Points: 380, Visits: 109
Hi,

For SQL 2005 or after, you can get current job execution time from the view
msdb.dbo.sysjobactivity.
Post #840211
Posted Tuesday, August 19, 2014 5:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 15, 2014 10:08 AM
Points: 1, Visits: 128
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.
Post #1605245
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse