December 29, 2014 at 12:54 pm
I'm looking for feedback on a query I've devised to return a numeric value if the 3 most recent executions of a job have failed. The purpose of the query is to server as a custom counter alert for Idera SQL DM, and by definition an SQL Script alert must return a numeric value.
My environment is SQL Server 2008. Please provide feedback if you have a more efficient query for accomplishing the task. Note that the job name is hard coded.
;WITH CTE_Restore_JobHistory AS
(SELECT h.[job_id]
,j.[name] as JobName
,h.[message]
,h.[run_status]
,h.[run_date]
,h.[run_time]
,ROW_NUMBER() OVER(PARTITION BY h.[job_id] ORDER BY h.[run_date], h.[run_time] DESC) AS RowID
FROM [msdb].[dbo].[sysjobhistory] h
INNER JOIN [msdb].[dbo].[sysjobs] j
ON h.[job_id] = j.[job_id]
WHERE j.[name] = 'LSRestore_205282-SQL1A_BHHCPassportRegistrationLog'
AND h.[run_status] != 4 -- Exclude jobs that are actively running
AND h.[step_id] = 0 -- Filter on Job Outcome step
)
, CTE_Restore_JobHistory2 AS
(SELECT *
FROM CTE_Restore_JobHistory
WHERE RowID in (1, 2, 3) -- Filter on the 3 most recent job executions
AND run_status = 0 -- Job has failed
)
, CTE_Restore_JobHistory3 AS
(SELECT [Job_ID], [JobName], COUNT(1) As Failure_Count
FROM CTE_Restore_JobHistory2
GROUP BY [Job_ID], [JobName]
HAVING COUNT(1) = 3
)
SELECT COUNT(1)
FROM CTE_Restore_JobHistory3
HAVING COUNT(1) > 0
December 29, 2014 at 2:16 pm
I think you need to order run_date DESC also, not just time:
ORDER BY h.[run_date] DESC, h.[run_time] DESC
Also, I don't see a need for a separate CTE_Restore_JobHistory2. You can go straight to the last CTE:
, CTE_Restore_JobHistory2 AS
(SELECT [Job_ID], [JobName], COUNT(1) As Failure_Count
FROM CTE_Restore_JobHistory
WHERE RowID IN (1, 2, 3) -- Filter on the 3 most recent job executions
AND run_status = 0 -- Job has failed
GROUP BY [Job_ID], [JobName]
HAVING COUNT(1) = 3
)
SELECT COUNT(1) AS Failure_Count
FROM CTE_Restore_JobHistory2
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 29, 2014 at 2:22 pm
Thanks, Scott. That was exactly the type of feedback I was looking for.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply