Solved

sql2014 - setup alert fro long runnig sql job

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.…
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…

867 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

12 Experts available now in Live!

Get 1:1 Help Now