1

Techies,

There's an application @my shop that gets executed through sql agent which works 95% of the time. The job completes within 10 minutes. The other 5% of the time, this same job stays active forever until one of us in the DBA team stop it. The developers are working on a fix, however, in the meantime--the job will need to run as is with the current flaw.

Without a budget for tools, I need to find some way to monitor this job so that if it spins for more over 15 minutes, the DBA team is at least alerted. Any suggestions?

2 Answers 2

4

I had the exact same situation and I created a SQL Agent job with the following script as the only step to monitor a job that occasionally runs long.

if exists(
        SELECT *
        FROM msdb.dbo.sysjobactivity AS sja
        INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
        WHERE sja.start_execution_date IS NOT NULL
           AND sja.stop_execution_date IS NULL and 
           sj.name ='Your job name'
        )
-- send an email 
exec msdb.dbo.sp_send_dbmail 
    @recipients ='email recipients',
    @subject='The job is running long',
    @body='Check the job on the server to see why it is running long'

In your case you would schedule this for 15 minutes after each execution of the job being monitored.

1
  • 2
    I scheduled mine for 2 hours after the execution of the job I was monitoring. In this case, the schedule would be for 15 minutes after each execution of the job being monitored. Commented Jul 20, 2016 at 20:23
2

Write a job that will run every 5 minutes using sys.dm_exec_requests and send you an email for anything with total_elapsed_time over 15 minutes.

I would recommend that everyone who has an OLTP environment, writes a job to monitor long running queries. It's much better to be proactive than to be reactive when it comes to long running SQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.