CREATE PROCEDURE dbo.usp_DBA_JobMonitor
AS
SET NOCOUNT ON
DECLARE @tmpTimeLen varchar(15),
@tmpDtLen varchar(15),
@newtime datetime,
@newInsertTime datetime,
@tmpNewTimeLen varchar(15),
@tmpNewDtLen varchar(15),
@tmpTime int,
@tmpJOBID varchar(50),
@TotalTime int,
@NewNumSamples int,
@maxThreshold int,
@minThreshold int,
@maxDt int,
@maxTm int,
@msg varchar(500),
@I int,
@x int,
@job_id uniqueidentifier,
@is_sysadmin int,
@job_owner sysname,
@JobName varchar(150),
@status int,
@state int
SET @job_id = NULL
SET @is_sysadmin = 0
SET @job_owner = NULL
CREATE TABLE #xp_results
(
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)
-- set sysadmin flag
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
-- set job owner
SELECT @job_owner = SUSER_SNAME()
-- populate #xp_results
INSERT INTO #xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
--select * from #xp_results order by job_state
CREATE TABLE #tmpJobIDs
(
id int identity (1,1),
job_id varchar(50)
)
INSERT INTO #tmpJobIDs (job_id)
SELECT job_id from #xp_results
SELECT @I=max(id) from #tmpJobIDs
SET @x=1
WHILE @x<=@I
BEGIN
SET @tmpTimeLen=''
SET @tmpDTLen=''
SET @newtime=null
SET @tmptime=0
SET @state=0
SELECT @tmpJOBID=job_id
FROM #tmpJobIDs
WHERE id=@x
-- ---- >> IF THE JOB IS NEW TO THE SYSTEM, INSERT IT INTO THE MONITOR
IF NOT EXISTS (SELECT * FROM msdb..dba_jobmonitor WHERE job_id=@tmpJOBID)
BEGIN
IF (SELECT last_run_date FROM #xp_results WHERE job_id=@tmpJOBID)>0
BEGIN
INSERT INTO msdb..dba_jobmonitor
SELECT top 1 @tmpJOBID,name, 0,1,'1900-01-01 00:00:00'
FROM msdb..sysjobs
WHERE job_id=@tmpJOBID
END
END
SELECT @state=job_state FROM #xp_results WHERE job_id = @tmpJOBID
-- >> IF THE JOB IS RUNNING, GET TIME IT HAS RUN SO FAR....
IF (@state=1)
BEGIN
SELECT @tmpDtLen=left(convert(varchar(10),next_run_date),4) + '-' +
substring(convert(varchar(10),next_run_date),5,2) + '-' +
right(convert(varchar(10),next_run_date),2)
FROM #xp_results
WHERE job_id=@tmpJOBID
SELECT @tmpTimeLen=next_run_time
FROM #xp_results
WHERE job_id=@tmpJOBID
WHILE len(@tmpTimeLen)<6
BEGIN
SET @tmpTimeLen='0' + @tmpTimeLen
END
SELECT @tmpTimeLen=left(@tmpTimeLen,2) + ':' + substring(@tmpTimeLen,3,2) + ':' + substring(@tmpTimeLen,5,2)
SELECT @newtime=convert
(
datetime,
(
@tmpDtLen + ' ' + @tmpTimeLen
)
)
SELECT @tmptime=DATEDIFF(s, @newtime, getdate())
END
ELSE
-- >> IF THE JOB IS NOT RUNNING, GET THE LAST RUN DATE
BEGIN
IF (select last_run_date FROM #xp_results WHERE job_id=@tmpJOBID)>0
BEGIN
-- >> CHECK TO SEE IF THE JOB FAILED, IF SO ... BYPASS THESE STEPS
SELECT @status=run_status
FROM msdb..sysjobhistory
WHERE job_id=@tmpJOBID and step_id=0
and run_date in
(
SELECT last_run_date
FROM #xp_results
WHERE job_id=@tmpJOBID
)
and run_time in
(
SELECT last_run_time
FROM #xp_results
WHERE job_id=@tmpJOBID
)
IF (@status=1)
BEGIN
SELECT @tmpDtLen=left(convert(varchar(10),last_run_date),4) + '-' +
substring(convert(varchar(10),last_run_date),5,2) + '-' +
right(convert(varchar(10),last_run_date),2)
FROM #xp_results
WHERE job_id=@tmpJOBID
SELECT @tmpTimeLen=last_run_time
FROM #xp_results
WHERE job_id=@tmpJOBID
WHILE len(@tmpTimeLen)<6
BEGIN
SET @tmpTimeLen='0' + @tmpTimeLen
END
SELECT @tmpTimeLen=left(@tmpTimeLen,2) + ':' + substring(@tmpTimeLen,3,2) + ':' + substring(@tmpTimeLen,5,2)
SELECT @newtime=convert
(
datetime,
(
@tmpDtLen + ' ' + @tmpTimeLen
)
)
-- >> CHECK THE LAST RUN DATE AGAINST THE LAST MONITORED JOB DATE
IF ((SELECT LastRunTime FROM msdb..dba_jobmonitor WHERE job_id=@tmpJOBID)<@newtime)
BEGIN
-- -->> GET LAST RUN TIME
SELECT @maxDt=MAX(run_date)
FROM msdb..sysjobhistory
WHERE step_id=0 and job_id=@tmpJOBID
SELECT @maxTm=MAX(run_time)
FROM msdb..sysjobhistory
WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt
IF (SELECT len(run_duration) from msdb..sysjobhistory where step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm)=5
BEGIN
SELECT @tmptime=convert(int,left(run_duration,1))*3600
FROM msdb..sysjobhistory
WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
SELECT @tmptime=@tmptime + convert(int,SUBSTRING(convert(varchar(10),run_duration),2,2))*60
FROM msdb..sysjobhistory
WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
SELECT @tmptime=@tmptime+convert(int,right(run_duration,2))
FROM msdb..sysjobhistory
WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
END
IF (SELECT len(run_duration) from msdb..sysjobhistory where step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm)=4
BEGIN
SELECT @tmptime=convert(int,left(run_duration,2))*60
FROM msdb..sysjobhistory
WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
SELECT @tmptime=@tmptime+convert(int,right(run_duration,2))
FROM msdb..sysjobhistory
WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
END
IF (SELECT len(run_duration) from msdb..sysjobhistory where step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm)=3
BEGIN
SELECT @tmptime=convert(int,left(run_duration,1))*60
FROM msdb..sysjobhistory
WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
SELECT @tmptime=@tmptime+convert(int,right(run_duration,2))
FROM msdb..sysjobhistory
WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
END
IF (SELECT len(run_duration) from msdb..sysjobhistory where step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm)<3
BEGIN
SELECT @tmptime=convert(int,left(run_duration,2))
FROM msdb..sysjobhistory
WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
END
END
END
END
END
IF (@tmptime>0)
BEGIN
-- ---- >> IF IT IS A NEW JOB INSERT CALCULATED RUN TIME FOR AVG DURATION
IF (SELECT LastRunTime FROM msdb..dba_jobmonitor WHERE job_id=@tmpJOBID)='1900-01-01 00:00:00'
BEGIN
UPDATE msdb..dba_jobmonitor
SET AvgRunDuration_Secs=@tmptime
WHERE job_id=@tmpJOBID
END
-- ----- >>DO THE CALCULATIONS FOR AVG-TIME
SELECT @TotalTime=AvgRunDuration_Secs*NumSamples
FROM msdb..dba_jobmonitor
WHERE job_id=@tmpJOBID
-- ---- >> SET THRESHOLD LEVELS
--This sets the time to 75% more or a quarter of what it should be
SELECT @maxThreshold=AvgRunDuration_Secs*1.75,@minThreshold=AvgRunDuration_Secs*.25
FROM msdb..DBA_JobMonitor WHERE job_id=@tmpJOBID
IF ((SELECT AvgRunDuration_Secs FROM msdb..DBA_JobMonitor WHERE job_id=@tmpJOBID)<300)
BEGIN
SET @maxThreshold=600
SET @minThreshold=0
END
-- -->> CHECK THE LAST RUN AGAINST MAX AND MIN THRESHOLD TO SEE IF WAS OUT OF THE ORDINARY
-- SELECT @minThreshold,@maxThreshold
IF (((@tmptime<@minThreshold) or (@tmptime>@maxThreshold)) and @state<>1) or (@tmptime>@maxThreshold and @state=1)
BEGIN
SELECT @msg=jobname + ' run duration was ' + convert(varchar(10),@tmptime) + ' seconds. The average run time is ' + convert(varchar(10),AvgRunDuration_Secs) + ' seconds.'
FROM msdb..dba_jobmonitor
WHERE job_id=@tmpJOBID
EXEC xp_sendmail @recipients = [b]'email@emailaddress.com'[/b],
@message = @msg,
--@copy_recipients = [b]'email@emailaddress.com'[/b],
@subject = 'Job Completed With Runtime Outside Of Threshold'
UPDATE msdb..DBA_JobMonitor
SET LastRunTime=dateadd(mi,1,@newtime)
WHERE job_id=@tmpJOBID
END
ELSE
-- -->> IF NOT OUT OF THE ORDINARY ADD IT INTO THE AVERAGE AND CONTINUE ON
BEGIN
IF (@state<>1)
BEGIN
SELECT @NewNumSamples=NumSamples + 1
FROM msdb..dba_jobmonitor
WHERE job_id=@tmpJOBID
SET @TotalTime=@TotalTime + @tmptime
UPDATE msdb..dba_jobmonitor
SET NumSamples=@NewNumSamples,AvgRunDuration_Secs=@TotalTime/@NewNumSamples
WHERE job_id=@tmpJOBID
UPDATE msdb..DBA_JobMonitor
SET LastRunTime=dateadd(mi,1,@newtime)
WHERE job_id=@tmpJOBID
END
END
END
SET @x=@x+1
END
DROP TABLE #tmpJobIDs
DROP TABLE #xp_results
GO
