SQL Server Performance

Mechanism to alert DBA when a SQL scheduled job runs for a long time in SQL2005

Discussion in 'SQL Server 2005 General DBA Questions' started by Ebenraja, Nov 1, 2010.

  1. Ebenraja Member

    Hi all,
    There is some problems with some of my SQL jobs related with netbackup strergy and applicational pakages as well, that run on sometime quite unusually a long time, so adversely they affect work.
    Is there any inbuilt SQL mechanism ,by which we can get alerted , if the job runs abnormally for a longer time. No 3rd party tools, but any systems tables, or DMV's anything inbuilt that can suit this.
    Appreciate if any of you have any queries by which abnormally long job duration can be monitored and have it alerted, might be we can use dbmail to alert, and the msdb job duration set can be queried, if any of you have any customated queries or resolutions, please do help me.
    Thanks
    Eben E
  2. Luis Martin Moderator

    I have a question to that.
    When SQL run this job, could be others users using, at the same time the database?.
    If no, then you can write some script to kill any process, said 5 minutes before, the jobs runs.
  3. Ebenraja Member

    Hi,
    Its SQL job that has netbackup step command's and it runs from a seperate netbackup server, the job duration was found to be around 4 hours and however the job usually complets in 1 hour.
    I agree with the accesment part as the job was force run when we faced the issue, it did not run in its usual time. I will find the flow of data during that time from netbackup end
    Since job shows as running status and ultimately the jobduration gets set in msdb table , but i do feel that it records the duration time only when it completes, if there is a possibility to alert the DBA that the job is running more than the usual time.
    Thanks
    Eben
  4. BRokke New Member

    I wrote my own stored procedure that I run hourly to check if I have any long running jobs and e-mails via DBMail. This is basically what I do:SET
    @SQLStmt = ''SELECT @SQLStmt = 'SELECT @lCountJobs = count(*) FROM msdb..sysjobactivity
    WHERE Datediff('
    + @szRunUnit + ',start_execution_date,getdate()) > ' + CONVERT(VARCHAR(10),@lRunValue)
    + ' and stop_Execution_date is null AND DATEDIFF (dd,start_execution_date,getdate()) < 2'
    SET @ParmDefinition = N'@lCountJobs int OUT'PRINT @SQLStmt
    EXECUTE sp_Executesql @SQLStmt,@ParmDefinition,@lCountJobs OUTIF @lCountJobs > 0
    BEGINSELECT @SQLStmt = 'SELECT @szJobName = j.name, @dtStartExecDate = ja.start_execution_date
    FROM msdb..sysjobs j join msdb..sysjobactivity ja
    ON j.job_id = ja.job_id
    WHERE Datediff(' + @szRunUnit + ',start_execution_date,getdate()) > '
    + CONVERT(VARCHAR(10),@lRunValue)
    + ' and stop_Execution_date is null AND DATEDIFF (dd,start_execution_date,getdate()) < 2'
    SET @ParmDefinition = N'@szJobName nvarchar(128) OUT,@dtStartExecDate datetime OUT'
    EXECUTE sp_Executesql @SQLStmt,@ParmDefinition,@szJobName OUT,@dtStartExecDate OUT
    SELECT @vcSubject = @@SERVERNAME + '-' + @szJobName + ' has been running since ' + CONVERT(VARCHAR(35),@dtStartExecDate)SELECT @QUERY = 'SELECT j.name, j.enabled,j.description,ja.start_execution_date from msdb..sysjobs j join msdb..sysjobactivity ja
    on j.job_id = ja.job_id
    WHERE Datediff(' + @szRunUnit + ',start_execution_date,getdate()) > '
    + CONVERT(VARCHAR(5),@lRunValue)
    + ' and stop_Execution_date is null AND DATEDIFF (dd,start_execution_date,getdate()) < 2'PRINT @QUERY
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBA',@recipients
    = @szEmailAddresses,@body = 'Long Running Jobs
    '
    ,@query
    = @QUERY,@subject
    = @vcSubject
  5. Ebenraja Member

    Hi Brooke,
    I am not a good developer , i feel that the stored procedure to be very useful, but i get declaration erorrs , when i execute, can you help in fine tuning this script
    There is an Execute statement in between, so does this revoke by itself using any PROC option (not able to find) as u configure it to run every hour.
    Help me to have this statement complete
    Messages:Msg 137, Level 15, State 1, Line 1
    Must declare the scalar variable "@SQLStmt".
    Msg 137, Level 15, State 2, Line 4
    Must declare the scalar variable "@szRunUnit".
    Msg 137, Level 15, State 1, Line 8
    Must declare the scalar variable "@ParmDefinition".
    Msg 137, Level 15, State 2, Line 10
  6. satya Moderator

    Look at www.codeplex.com site and search for such solutions, they are many out there.
    Do you have any ALERTING software in yoru company?
  7. Ebenraja Member

    its quite late now to give a reply, was quite held in a lot of mess ... yeah we do have OEM to monitor MS SQL through plugin , but the tool is not so productive for Windows.....
    will check that site for code....
    thanks
  8. satya Moderator

    Do you have any third party tools in place for monitoring the platform?
    Are you willing to overview any SQL related third party tools in this regard?

Share This Page