Solved

sql2014 - setup alert fro long runnig sql job

Posted on 2016-08-13
3
67 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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Resolve DNS query failed errors for Exchange
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
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…

830 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