asked on

Need to restart the SQL job if it's running morethan 30 minutes

Hello,

I have a SQL job called "ALL - DelHistory" which occupationally gets hung or running morethan 30 minutes.In that case,it should stop the job,wait for 5 minutes and should start the job again.

I have created a another sql job to montior ALL-DelHistory job, but script below is not working.Please modify the script to achieve my goal.

USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
 
ALTER PROCEDURE [dbo].[Monitoring_all_del_history_job] (
 
          @threshold_minute int = 30,       -- Default is set to 30 minutes
 
          @StopJob bit = 0,                  -- Default is not to stop the job 
 
          @ExcludeJOb XML =NULL -- '<ExcludeJob><JobName JOB="Export TMF PerfData"/></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 
 
     AND j.name  ='ALL - DelHistory'                                          --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)
 
    
 
 
 
     IF @StopJob = 1
 
     BEGIN
 
          PRINT 'Stopping job [' + @Jobname + ']'
 
          EXEC msdb..sp_stop_job @job_name=@JobName
 
          waitfor delay '00:05:00'
 
          PRINT 'Starting job [' + @Jobname + ']'
 
          EXEC msdb..sp_start_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

Open in new window

David Todd

Hi,

Can you expand on what you mean when you say - Its Not Working?

That is, it does nothing, it kills said job, but doesn't restart it, ... what?

Regards
David