Solved

Need to modify the sp's

Posted on 2012-04-02
6
281 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

943 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

10 Experts available now in Live!

Get 1:1 Help Now