Need a double monitoring for SQL job's

VIVEKANANDHAN_PERIASAMY
VIVEKANANDHAN_PERIASAMY used Ask the Experts™
on
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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hello, isn't as simple as modifying this:
@threshold_minute int = 30?
Nope,

sp's should work like below in sequence

1. Sp's should check if any jobs are running for morethan 30 min's and if any SQL job is running  ---------------1st checkpoint
 1a. then it should stop the Sql job
 1b. start the SQL job
 1c. Mail the Status & name of the Job.
2. Due to some issue if  above check failed then script should monitor SQL Job running morethan 60 mins. -------------2nd checkpoint
 2a. if it finds any SQL job running more than 60 mins then it should write into event logs.

Commented:
Don't forget that if a job runs 30 minutes and it's deliberatly or accidently in one single transaction if you stop the job it has a long rollback time also.
no problem. I need it in a below sequences.

1. Sp's should check if any jobs are running for morethan 30 min's and if any SQL job is running  ---------------1st checkpoint
 1a. then it should stop the Sql job
 1b. start the SQL job
 1c. Mail the Status & name of the Job.
2. Due to some issue if  above check failed then script should monitor SQL Job running morethan 60 mins. -------------2nd checkpoint
 2a. if it finds any SQL job running more than 60 mins then it should write into event logs.
DB Expert/Architect
Top Expert 2011
Commented:
Hi,

Please verify whether following code is enough:
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>'
          @mail_profile VARCHAR(255)=NULL,
          @mail_recipients VARCHAR(512)='test@company.com'
          )
 
AS
 
SET NOCOUNT ON
 
DECLARE @is_sysadmin     INT
DECLARE @job_owner       SYSNAME
DECLARE @job_id         UNIQUEIDENTIFIER
DECLARE @JobName         VARCHAR(255)
DECLARE @JobStartTime    DateTime
DECLARE @JobRunningTime TINYINT
DECLARE @Msg            VARCHAR(1000)
DECLARE @ExcludeJObTable TABLE (JobName VARCHAR(1000))
DECLARE @Body varchar(max)
DECLARE	@TableHead varchar(max)
DECLARE	@Table_Content varchar(max)
DECLARE @TableTail varchar(max)
DECLARE @mail_subject VARCHAR(255)

Set @TableTail = '</table></body></html>';

Set @TableHead = '<html><head>' +
                  '<style>' +
                  'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
                  '</style>' +
                  '</head>' +
                  '<body>'
 
/** 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)

CREATE TABLE #xp_results_new (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, DATEDIFF(MINUTE,a.start_execution_date,GETDATE()) AS JobRunningTime
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, @JobRunningTime
 
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  
     @JobRunningTime>30)
     BEGIN
		IF @JobRunningTime<60
		BEGIN
			PRINT 'Stopping job [' + @Jobname + ']'
			EXEC msdb..sp_stop_job @job_name=@JobName
			WAITFOR DELAY '00:00:05'
			PRINT 'Starting job [' + @Jobname + ']'
			EXEC msdb..sp_start_job @job_name=@JobName
			
			INSERT INTO #xp_results_new
			EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
			
			SET @TableTail = '</table></body></html>';
			SET @Table_Content = @TableHead+'<table cellpadding=0 cellspacing=0 border=0>' +
                  '<tr bgcolor=#FFEFD8><td align=center><b>Job Name</b></td>' + 
                  '<td align=center><b>Is Running</b></td>' +
                  '<td align=center><b>Current Step</b></td>' +
                  '<td align=center><b>Current Rettry Attempt</b></td>' +
                  '<td align=center><b>Job State</b></td></tr>'
			SELECT @Body = (SELECT ROW_NUMBER() OVER(ORDER BY x.job_id) % 2 AS [TRRow],
            sj.name AS [TD],
            CASE running
				WHEN 0 THEN 'Stopped'
				WHEN 1 THEN 'Running'
			END AS [TD],
            current_step AS [TD],
            current_retry_attempt AS [TD],
            CASE job_state
				WHEN 0 THEN 'Not idle or suspended'
				WHEN 1 THEN 'Executing'
				WHEN 2 THEN 'Waiting For Thread'
				WHEN 3 THEN 'Between Retries'
				WHEN 4 THEN 'Idle'
				WHEN 5 THEN 'Suspended'
				WHEN 6 THEN 'Waiting For Step To Finish'
				WHEN 7 THEN 'Performing Completion Actions'
			END AS [TD]
			FROM #xp_results_new x
			JOIN msdb.dbo.sysjobs sj ON x.job_id=sj.job_id
            FOR XML RAW('tr'), ELEMENTS)
			
			SET @Body = Replace(@Body, '_x0020_', space(1))
			SET @Body = Replace(@Body, '_x003D_', '=')
			SET @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
			SET @Body = Replace(@Body, '<TRRow>0</TRRow>', '')

			SELECT @Body = @TableHead + @Table_Content + @Body + @TableTail
			
			SET @mail_subject = '[!!!WARNING!!!] Job '+QUOTENAME(ISNULL(@JobName,'UNKNOWN'))+' is running longer than 30 minutes'
			
			EXEC msdb.dbo.sp_send_dbmail 
			@recipients=@mail_recipients,
			@subject = @mail_subject,
			@body = @Body,
			@body_format = 'HTML' ;  
			
			TRUNCATE TABLE #xp_results_new;      
		END
			IF @JobRunningTime>=@threshold_minute
			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
				IF @StopJob = 1
					BEGIN
						PRINT 'Stopping job [' + @Jobname + ']'
						EXEC msdb..sp_stop_job @job_name=@JobName
					END
			END
     END
 
     FETCH NEXT FROM joblist_cursor
     INTO @JobName, @job_id, @JobStartTime
END
 
CLOSE joblist_cursor
DEALLOCATE joblist_cursor
DROP TABLE #xp_results
DROP TABLE #xp_results_new

Open in new window


Discussion is continued in following thread:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_27750832.html#a38069655

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial