Solved

Need to modify the sp's

Posted on 2012-04-02
6
289 Views
Last Modified: 2012-04-06
I have a Sp which will monitor all SQL jobs,and if any of the SQL jobs run morethan 60 minutes then it will raise a 50000 errors.It difficult/vague to find out which SQL jobs was  taking morethan 60 mins.

So i want to modify the SP which should give the name of SQL job which taking morethan 60 mins rather than giving 50000 error.

SP:

USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
 
 
 
 
/******************************************************************************************
**
** Name: Monitoring job.sql
**
** Parm: @Thresdhold_minute int  - Threshold value in munites
**        @StopJob int            - Action flag to tell the script to perform an action
**                                 Set to 0 will only Alarm
**                                 Set to 1 will Alarm and Stop the job
**
** Desc: This procedure checks the active running SQL jobs and if the job is running longer than
**       the threshold set (i.e. 60 minutes), it will do the following:
**       1. Sent the alarm
**       2. Per the @StopJob flag - Stop the SQL job if the lag is set to 1
**        
**
If we need to remove multiple jobs from monitoring
 
            Pass the xml string as :
            '<ExcludeJob><JobName JOB="Job1"/></ExcludeJob><ExcludeJob><JobName JOB="Job2"/></ExcludeJob>'
           
*******************************************************************************************/
 
 
ALTER PROCEDURE [dbo].[Monitoring job] (
          @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="Export FMT 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                                               -- 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 wherejob_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 ANDstop_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
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 37795019
In your cursor you have dbo.sysjobs, there you find both 'name' as 'description'
http://msdn.microsoft.com/en-us/library/ms189817.aspx
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 37795684
is this correct?

SELECT j.name as name, j.description as description 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 wherejob_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
0
 
LVL 25

Expert Comment

by:jogos
ID: 37796149
SELECT j.name as name, j.description as description ,a.job_id, a.start_execution_date  -- -- -- with a , after description

The only one who can see if it gives what you want is you by trying the sql on your sql.

If I can have my say about your SQL and for this it won't be that much of a performance difference but when you deal with large tables and more complex joins it could.

Exists is mostly a better performer as (NOT) IN.  
AND not exists 
   (SELECT exc.Jobname from @ExcludeJObTable exc  
    where exc.Jobname = j.name )               --Excluding the job Monitoring

Open in new window


Checkprocedure dbo.sp_help_job if you use the parameter @execution_status to filter the active jobs then you don't need that mix between  using a procedure to fill a temp-table and getting same info again in your cursor but getting it from the systables.
http://msdn.microsoft.com/en-us/library/ms186722.aspx
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 5

Expert Comment

by:25112
ID: 37796177
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 37800507
I tried but I am not getting te information.

SELECT @Msg = 'The SQL Job - [' + @JobName + '] on ' + @@ServerName + ' has exceeded the job running threshold of ' + CONVERT(varchar(3), @threshold_minute) + ' minutes.'

My servername is NULL, Is it because of that?
0
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 37800618
If you concatinate strings .... if one is null then result is null. I doupt @@Serverneme is null. But place isnull() around it (and all variables)  and you're sure.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question