Solved

Need to modify the sp's

Posted on 2012-04-02
6
279 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
  • 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now