Need a double monitoring for SQL job's

I have SQL job which will monitor other SQL job and if any jobs running morethan 60 mins,then it will write a error into eventlog.
But i need a help to modify this SP's which should check if any jobs are running morethan 30 mins then it should stop the SQL job
and start the job again.And should mail the job details
By default it should not monitor "SQL SERVER JOB".
But monitoring for 60 minutes also should happened. If at all by any means,checkpoint of 30 minutes is missed then 60 minutes montioring
should write into events logs.

Basically I want two montioring for SQL jobs.

Script:

ALTER PROCEDURE [dbo].[SQL_Job_Monitor] (
          @threshold_minute int = 60,       -- Default is set to 60 minutes
          @StopJob bit = 0,                  -- Default is not to stop the job 
          @ExcludeJOb XML =NULL -- '<ExcludeJob><JobName JOB="SQL SERVER JOB"/></ExcludeJob>'
          )
 
AS
 
SET NOCOUNT ON
 
DECLARE @is_sysadmin     INT
DECLARE @job_owner       sysname
DECLARE @job_id         UNIQUEIDENTIFIER
DECLARE @JobName         VARCHAR(255)
DECLARE @JobStartTime    DateTime
DECLARE @Msg            VARCHAR(1000)
DECLARE @ExcludeJObTable TABLE (JobName VARCHAR(1000))
 
/** Insert the jobs to be excluded from job monitoring  */
INSERT @ExcludeJObTable (JobName)
SELECT xmllist.rows.value('@JOB','varchar(1000)') 
from @ExcludeJOb.nodes('/ExcludeJob/JobName') xmllist(rows)
 
-- Step 1: Create intermediate work tables to store the running job data
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, -- BOOL
                       request_source        INT              NOT NULL,
                       request_source_id     sysname          NULL,
                       running               INT              NOT NULL, -- BOOL
                       current_step          INT              NOT NULL,
                       current_retry_attempt INT              NOT NULL,
                       job_state             INT              NOT NULL)
 
-- Step 2: Capture job execution information (for local jobs only since that's all SQLServerAgent caches)
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME() 
IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater
BEGIN
    INSERT INTO #xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
END
ELSE
BEGIN
     INSERT INTO #xp_results
     EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
END
 
-- Step 3: Get the current job that exceeded the monitoring threshold
 
DECLARE joblist_cursor CURSOR FOR
SELECT j.name, a.job_id, a.start_execution_date
FROM #xp_results x
INNER JOIN msdb.dbo.sysjobactivity a
ON x.job_id = a.job_id
INNER join msdb.dbo.sysjobs j
ON a.job_id = j.job_id
     WHERE x.running = 1                                               -- The job is running
     AND j.name  not IN  (SELECT Jobname from @ExcludeJObTable)               --Excluding the job Monitoring
     AND a.session_id = (select max(session_id) from msdb.dbo.sysjobactivity where job_id = a.job_id)
     AND a.stop_execution_date is null                                 -- The job is not complete 
     AND a.start_execution_date <= dateadd(minute, -@threshold_minute, getdate())     -- Measure the thresdhold
 
OPEN joblist_cursor
 
FETCH NEXT FROM joblist_cursor
INTO @JobName, @job_id, @JobStartTime
 
WHILE @@FETCH_STATUS = 0
BEGIN
     -- Check again if the same job at the starting time is still active
     IF EXISTS (SELECT TOP 1 * FROM msdb.dbo.sysjobactivity WHERE job_id = @job_ID AND stop_execution_date is NULL AND start_execution_date = @JobStartTime)
     BEGIN
          SELECT @Msg = 'The SQL Job - [' + @JobName + '] on ' + @@ServerName + ' has exceeded the job running threshold of ' + CONVERT(varchar(3), @threshold_minute) + ' minutes.'
          RAISERROR (@Msg, 16, 1) WITH LOG
     END
 
     IF @StopJob = 1
     BEGIN
          PRINT 'Stopping job [' + @Jobname + ']'
          EXEC msdb..sp_stop_job @job_name=@JobName
     END
 
     FETCH NEXT FROM joblist_cursor
     INTO @JobName, @job_id, @JobStartTime
END
 
CLOSE joblist_cursor
DEALLOCATE joblist_cursor
DROP TABLE #xp_results

Select allOpen in new window