Solved

sql2014 - setup alert fro long runnig sql job

Posted on 2016-08-13
3
79 Views
Last Modified: 2016-08-24
Guys,
In sql server 2008 or sql 2014, is there any option available in  sql alert to set the alert to trigger when the job running exceed some expected hours ?
0
Comment
Question by:motioneye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
DcpKing earned 400 total points
ID: 41755604
I am not aware of such. However, you can always write a very simple job that can run every few minutes while the potentially problematic job is running. It can use some fairly simple arithmetic to determine how long a step has been running

1. get your job's name
2. run this to obtain the job ID (field job_id):
select * from msdb.dbo.sysjobs where name = 'MyJobName'

Open in new window

3. Use this code to obtain data about when the step(s) started, using the job_id GUID from the last step:
select * from msdb.[dbo].[sysjobactivity] where job_id = 'FD3B9B1F-BF56-4125-B937-3D8E2EC92C81'

Open in new window

4. You'll see start_execution_date and stop_execution_date. The former gets filled at start-time, and the latter at end-time, so the job is still running if the stop_execution_date is null. This will give you the runtime:
datediff(seconds,  start_execution_date, getdate()) 

Open in new window

5. You can also look at the job history with this:
select * from msdb.[dbo].[sysjobhistory] where job_id = 'FD3B9B1F-BF56-4125-B937-3D8E2EC92C81'

Open in new window

and look at the start and stop times to see if the duration is steadily increasing, or if it suddenly increases after something like a large data load occurs.

If your monitoring job does see a problem then it can, for example, alert you with an email ...

Hope this helps

Mike
(code checked on SQL Server 2014 - the system tables might have changed from 2008)
0
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 100 total points
ID: 41756038
You can always control the job execution duration inside the job itself.
As first step of the job record the start time and during the job place controls in strategic places inside the code and when the execution time exceeds the threshold you set the raise an error so the alert will be fired.
0
 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 400 total points
ID: 41757264
Comment on Vitor Montalvão's suggestion:
If it's just one task (like a dataflow, for example) that suddenly takes far too long then it's difficult to have the job control itself, as by the time it's over-run the time you want to run it for it's already in a task and will have to wait until it gets back! Hence my suggestion of using an independent supervisor job ....

hth

Mike
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

The following article is comprised of the pearls we have garnered deploying virtualization solutions since Virtual Server 2005 and subsequent 2008 RTM+ Hyper-V in standalone and clustered environments.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…

691 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