Detect longer jobs than usual

  • 17 March 2023
  • 0 replies
  • 32 views

Userlevel 2
Badge +3

There’s some alert configuration to detect longer jobs than usual, also you can check the jobs the same condition in Job Controller (small icon would appear).

Still there’s not so easy way to detect the delay with some sort of custom criteria, like for specific client, duration which would take double or three times longer as usual, etc.

 

This query is intended to address some requests, listing up the difference between running jobs durations and “average” per subclient and backup level.

use commserv

set transaction isolation level read uncommitted

select
bkji.jobId
,bkji.applicationId
,apc.name as 'clientname'
,apap.subclientName
,bkji.bkpLevel
,1.0 * (dbo.GetUnixTime(GETUTCDATE()) - ji.jobStartTime) / grp.avg_duration as 'exceeded'
,grp.avg_duration
,grp.count_job
from jmbkpjobinfo bkji
inner join JMJobInfo ji on bkji.jobid = ji.jobid
inner join APP_Application apap on apap.id = bkji.applicationId
inner join APP_Client apc on apc.id = apap.clientId
inner join (
select appId, bkpLevel, avg(duration) as avg_duration, avg(totalBackupSize) as avg_totalBackupSize, count(jobid) as count_job from JMBkpStats
where status = 1
group by appId, bkpLevel
) as grp on grp.appId = bkji.applicationId and grp.bkpLevel = bkji.bkpLevel
order by bkji.jobId desc

From here, you can see the running job(s) are taking rather longer time or not.

 

Hope this helps,


0 replies

Be the first to reply!

Reply