Capture SQL jobs running longer than x minutes
-
I am trying to work on a solution that would capture SQL jobs in a table that ran longer than say x minutes (it can be configurable) during the entire course of the day based on the job history and dump the result set into a table that I can loop through during the start of my day that would help me figure out what SQL job ran longer. One important thing here would be if a job runs just once then it won't be an issue but if it runs multiple times during the day say every hr. then I would only like to dump the execution that took longer. I tried to google for the solution but I couldn't find something that fits my criteria. Any help would be much appreciated. I know for sure many of you would be doing this so it is definitely not re-inventing the wheel.
-
The information is captured in system tables. You just have to query those and you're off and running. Take a look at sys.jobhistory. Then, to only get the longest one, SELECT TOP 1 .... ORDER BY Duration. Ta da!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
In Management Studio try to open the custom report attached
it will show you sqljobs time line for a whole day grouped by 30 minutes chunks (intervals)
if you click on an interval you will see details (per minute information)
The pic below is not a good example (my server is Express edition), but nonetheless...
- Grant Fritchey wrote:
The information is captured in system tables. You just have to query those and you're off and running. Take a look at sys.jobhistory. Then, to only get the longest one, SELECT TOP 1 .... ORDER BY Duration. Ta da
I hope it could be that simple. Do you have a working example on how to logically put this through. I queried that sys.jobhistory table but I don't want to get my duration based on steps. I would like to have the final job result and order it by duration.
-
You can try playing with something like the following to grab jobs that were successful and just look at the duration of the job (instead of each job step):
SELECT
j.name,
run_date,
max(h.run_duration) RunDuration
FROM dbo.sysjobhistory h
INNER JOIN dbo.sysjobs j
ON h.job_id = j.job_id
WHERE step_name like '(Job outcome)%'
AND run_status = 1
GROUP BY j.name, run_date
ORDER BY RunDuration descSue
-
Here's a sample query that finds jobs that run a certain % of time over previous runs. Naturally you can tweak the percentages and prior run counts as you prefer to:
IF OBJECT_ID('tempdb.dbo.#job_duration_factors') IS NOT NULL
DROP TABLE #job_duration_factors
CREATE TABLE #job_duration_factors (
run_duration_mins_min int NOT NULL,
run_duration_mins_max int NOT NULL,
required_percentage_over decimal(5, 2) NOT NULL
)
INSERT INTO #job_duration_factors VALUES
( 0, 5, 100.00),
( 6, 10, 80.00),
(11, 15, 60.00),
(16, 20, 50.00),
(21, 30, 33.33),
(31, 45, 25.00),
(46, 60, 15.00),
(61,999999, 10.00)
SELECT j.name,
MAX(CASE WHEN jh.row_num = 1 THEN jh.run_duration_mins ELSE 0 END) AS last_run_time_mins,
SUM(CASE WHEN jh.row_num > 1 THEN 1 ELSE 0 END) AS prior_run_count,
MAX(CASE WHEN jh.row_num > 1 THEN jh.run_duration_mins ELSE 0 END) AS max_mins_prior_runs
FROM (
SELECT
run_duration / 10000 * 60 + run_duration % 10000 / 100 AS run_duration_mins,
ROW_NUMBER() OVER(PARTITION BY job_id ORDER BY instance_id DESC) AS row_num,
*
FROM msdb.dbo.sysjobhistory
WHERE step_id = 0 AND run_status = 1
) AS jh
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id
INNER JOIN #job_duration_factors jdf ON
jh.run_duration_mins BETWEEN jdf.run_duration_mins_min AND jdf.run_duration_mins_max
WHERE jh.row_num BETWEEN 1 AND 4
GROUP BY j.name
HAVING
(MAX(CASE WHEN jh.row_num = 1 THEN jh.run_duration_mins ELSE 0 END) -
MAX(CASE WHEN jh.row_num > 1 THEN jh.run_duration_mins ELSE 0 END)) * 100.0 /
NULLIF(MAX(CASE WHEN jh.row_num > 1 THEN jh.run_duration_mins ELSE 0 END), 0) >
MAX(jdf.required_percentage_over)SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
This was removed by the editor as SPAM
- Sue_H wrote:
You can try playing with something like the following to grab jobs that were successful and just look at the duration of the job (instead of each job step):
SELECT
j.name,
run_date,
max(h.run_duration) RunDuration
FROM dbo.sysjobhistory h
INNER JOIN dbo.sysjobs j
ON h.job_id = j.job_id
WHERE step_name like '(Job outcome)%'
AND run_status = 1
GROUP BY j.name, run_date
ORDER BY RunDuration descSue
Thanks! Sue. However, I am looking for something wherein I can check for the run duration for the past 24 hrs. and only dump the jobs in the table that are within that time window. Your code gives me a lot more than I actually need to consume. Do you have something of that nature.
-
You would just filter on run_date to work in your scenario or possibly include run_time if you are doing last 24 hours.
Sue
- Feivel wrote:Sue_H wrote:
You can try playing with something like the following to grab jobs that were successful and just look at the duration of the job (instead of each job step):
SELECT
j.name,
run_date,
max(h.run_duration) RunDuration
FROM dbo.sysjobhistory h
INNER JOIN dbo.sysjobs j
ON h.job_id = j.job_id
WHERE step_name like '(Job outcome)%'
AND run_status = 1
GROUP BY j.name, run_date
ORDER BY RunDuration descSue
Thanks! Sue. However, I am looking for something wherein I can check for the run duration for the past 24 hrs. and only dump the jobs in the table that are within that time window. Your code gives me a lot more than I actually need to consume. Do you have something of that nature.
MSDB has a "dbo.agent_datetime" function built in. If you pass it the RunDate column and the RunTime column, it will return a DATETIME that you could compare the run date and time with in the WHERE clause. It's not normally a good practice to do such a thing but the design of the MSDB tables don't allow for much else.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply