Solved

Need to modify the sp's

Posted on 2012-04-02
6
285 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

856 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