?
Solved

sql2014 - setup alert fro long runnig sql job

Posted on 2016-08-13
3
Medium Priority
?
85 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 1600 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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 400 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 1600 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A procedure for exporting installed hotfix details of remote computers using powershell
In this Micro Tutorial viewers will learn how to use Windows Server Backup to create full image of their system. Tutorial shows how to install Windows Server Backup Feature on Windows 2012R2 and how to configure scheduled Bare Metal Recovery backup.…
In this Micro Tutorial viewers will learn how to restore single file or folder from Bare Metal backup image of their system. Tutorial shows how to restore files and folders from system backup. Often it is not needed to restore entire system when onl…
Suggested Courses

762 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