April 10, 2007 at 1:11 pm
Hi everybody!
I was wondering what does the step_id 0 means in jobs and whether every job has that step or not. I think (as the job name says) is the job outcome and yes, every job has the step 0.
What I'm trying to do is to find which jobs failed and when in order to get the percentage of failure they have. I'm querying the SysJobHistory table in the msdb database but it contains one row per step rather per job (as I want it)
So I thought I could use a query like this one (not sure if it will always work the way I need it):
SELECT job_id, run_date
FROM msdb.dbo.SysJobHistory
WHERE step_id = 0 AND run_status = 0
BTW, I actualy use this one and I think it works but I'm trying to find an easier way to get the same results:
SELECT DISTINCT JH.job_id, JH.run_date
FROM msdb.dbo.SysJobHistory AS JH
WHERE EXISTS (
SELECT 1
FROM msdb.dbo.SysJobHistory
WHERE JH.job_id = job_id AND JH.Step_Id = Step_Id
AND JH.run_date = run_date AND JH.run_time = run_time
AND run_status = 0)
Any help will be apreciated =D.
Thanks in advance!
April 10, 2007 at 2:53 pm
Every job does have Step 0. It is the job outcome. Open Enterprise Manager. Expand Management. Click on Jobs. Right click on any job that has run and select View Job History. Check the box for Show Step Details. You will see Step 0.
-SQLBill
April 10, 2007 at 3:04 pm
Thank you Bill. BTW: I'm trying to obtain this information via T-SQL. I think I got it now:
SELECT J.name AS JobName, COUNT(*) AS TotalExecutions
, CONVERT(DECIMAL, ISNULL(JF.FailureQuantity, 0))*100.00/CONVERT(DECIMAL,COUNT(*)) AS FailureRate
FROM msdb.dbo.SysJobHistory AS JH
INNER JOIN dbo.SysJobs AS J ON JH.Job_Id = J.Job_Id
LEFT OUTER JOIN (
--Quantity of failed executions per job
SELECT A.Job_id, COUNT(*) AS FailureQuantity
FROM (
SELECT JH.Job_id, J.name AS JobName, JH.run_date, JH.run_time, JH.run_duration
FROM msdb.dbo.SysJobHistory AS JH
INNER JOIN msdb.dbo.SysJobs AS J ON JH.Job_Id = J.Job_Id
WHERE step_id = 0 AND run_status = 0 AND J.Enabled = 1
) AS A
GROUP BY A.Job_id) AS JF ON JH.job_id = JF.Job_id
WHERE JH.step_id = 0 AND J.Enabled = 1
GROUP BY J.name, JF.FailureQuantity
HAVING CONVERT(DECIMAL, ISNULL(JF.FailureQuantity, 0))*100.00/CONVERT(DECIMAL,COUNT(*)) > 0
ORDER BY FailureRate DESC
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply