November 18, 2014 at 8:12 pm
Hi All,
I just want to know the possibility of bellow scenario. Please advise how can proceed with below
Scenario:
We need to maintain the threshold for each job on execution time in DB, if in case job is going beyond threshold we need to get alert so that we can get attention
November 19, 2014 at 4:31 am
AFAIK some third party monitor applications are able to do what you need, although I don't have any experience with them.
If you want to build your own solution you should take this approach:
- Query the msdb..sysjobhistory table and extract all durations for all (succesfull) jobruns. Calculate the average for each job. Create a monitoring job that takes all running jobs from the msdb..sysjobactivity table, looks at the start_execution_date and calculate the current duration. Generate a warning when the threshold is reached.
Because the msdb..sysjobhistory is cleaned regularly, you could end up with too little information about jobruns from the past. So perhaps you should periodically copy the msdb..sysjobhistory table to a staging table.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply