Solved

sql2014 - setup alert fro long runnig sql job

Posted on 2016-08-13
3
46 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
  • 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 45

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
What to do when Windows Update is not working correctly? What tools can I use to detect the cause of the malfunction problem? What does this numeric error code mean? These and other questions that you have been asking in the past are answered here (…
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
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…

744 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

11 Experts available now in Live!

Get 1:1 Help Now