Need to modify the sp's

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
LVL 5
VIVEKANANDHAN_PERIASAMYAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jogosCommented:
In your cursor you have dbo.sysjobs, there you find both 'name' as 'description'
http://msdn.microsoft.com/en-us/library/ms189817.aspx
0
VIVEKANANDHAN_PERIASAMYAuthor Commented:
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
jogosCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

VIVEKANANDHAN_PERIASAMYAuthor Commented:
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
jogosCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.