Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I' using a SQL Server 2008 Sp3 environment.

I have a query that returns information about failed jobs from the last 10 minutes. If a job has two schedulers (for example - a job that runs every 10 minutes in the morning, and every 30 minutes in the rest of the day), the query returns two rows. Can you help me fix the query so it would return only the relevant row of the relevant scheduler that was used ?

The query I'm using is pasted below:

SELECT h.server
,j.name
,h.step_id
,h.step_name
,h.message
,h.run_status
,Rundatetime        = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
,h.run_duration
,s.last_outcome_message         
,sc.name AS ScheduleName
,s.last_run_time
,sjc.next_run_time
FROM msdb..sysjobhistory h 
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb..sysjobservers  s ON s.job_id=h.job_id
LEFT OUTER JOIN msdb..sysjobschedules  sjc ON sjc.job_id=h.job_id
LEFT OUTER JOIN msdb..sysschedules  sc ON sc.schedule_id=sjc.schedule_id
WHERE run_status=0
AND step_name<>'(Job outcome)'
--Looking on failed job from the last 10 minutes.
AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -10, GETDATE())
ORDER BY h.run_date DESC ,h.run_time DESC

Thanks in advance, Roni.

share|improve this question

3 Answers 3

up vote 1 down vote accepted

This is a little scrappy, but it is a start...

I have extracted the Schedule ID from the [last_outcome_message] field, and joined it to the Schedule ID. That should give you the correct result set

I could not find anywhere else where the schedule_id is stored against a History Event.

SELECT h.server
,j.name
,h.step_id
,h.step_name
,h.message
,h.run_status
,Rundatetime        = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
,h.run_duration
,s.last_outcome_message         
,sc.name AS ScheduleName
,s.last_run_time
,sjc.next_run_time
FROM msdb..sysjobhistory h 
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb..sysjobservers  s ON s.job_id=h.job_id
LEFT OUTER JOIN msdb..sysjobschedules  sjc ON sjc.job_id=h.job_id

     AND CONVERT(INT, LTRIM(RTRIM(REPLACE(LEFT(last_outcome_message, 
                   PATINDEX('% (%', last_outcome_message)), 
                   'The job failed.  The Job was invoked by Schedule ', ''))))
          = sjc.schedule_id

LEFT OUTER JOIN msdb..sysschedules  sc ON sc.schedule_id=sjc.schedule_id
WHERE 
run_status=0
AND step_name<>'(Job outcome)'
--Looking on failed job from the last 10 minutes.
AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -10, GETDATE())
ORDER BY h.run_date DESC ,h.run_time DESC
share|improve this answer
    
Nice approach with taking the info from "last_outcome_message" in the WHERE clause. Didn't think on that. will try it tomorrow or the next day. will come back to update here. –  Roni Vered Mar 5 '13 at 22:54
    
Thanks, I hope it helps. –  RoKa Mar 6 '13 at 9:12
    
Hi, I ran the query as is, and it failed with the error "Conversion failed when converting the nvarchar value 'The job succeeded. The Job was invoked by Schedule 26' to data type int." –  Roni Vered Mar 8 '13 at 8:49
1  
But the approach is coorect ! I've amended a bit your suggestion. Change the additional join condition with msdb..sysjobschedulers with the following like: AND CAST (LEFT (SUBSTRING (s.last_outcome_message , CHARINDEX (N'Schedule' , s.last_outcome_message) + 9 , LEN (s.last_outcome_message)) , CHARINDEX (N'(' , SUBSTRING (s.last_outcome_message , CHARINDEX (N'Schedule' , s.last_outcome_message) + 9 , LEN (s.last_outcome_message))) - 1) AS INT) = sjc.schedule_id –  Roni Vered Mar 8 '13 at 8:51

Doing a min on sjc.next_run_time should do the trick.

share|improve this answer
    
I've added the following limitation: and sjc.next_run_time = (SELECT MIN(a.next_run_time)FROM msdb..sysjobschedules a where a.job_id=h.job_id ) but it gives false results. –  Roni Vered Mar 5 '13 at 14:57

Would a simpler approach not be to look for the job outcome step? The message column will tell you what invoked the job.

--AND step_name='(Job outcome)'

SELECT h.server
,j.name
,h.step_id
,h.step_name
,h.message
,h.run_status
,Rundatetime        = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
,h.run_duration
FROM msdb..sysjobhistory h 
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb..sysjobservers  s ON s.job_id=h.job_id
WHERE run_status=0
AND step_name='(Job outcome)'
--Looking on failed job from the last 10 minutes.
AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -100, GETDATE())
ORDER BY h.run_date DESC ,h.run_time DESC
share|improve this answer
    
Mmmm, I've ran your suggested query and still got two rows for the two schedulers. Just create a simple job that do nothing with 2 schedulers and see it for yourself. –  Roni Vered Mar 5 '13 at 14:52
    
Sorry, pasted the wrong code snippet. If you join to schedules you will get two rows. There is no key on history that ties to the schedule - just the message column. –  Cate Donoghue Mar 5 '13 at 15:05
    
Thanks for the edit Marian, I'm obviously missing something here, as I could not get that to format correctly. :) –  Cate Donoghue Mar 5 '13 at 15:45
    
Alright, I'll check it tomorrow or the next day and will come back to update –  Roni Vered Mar 5 '13 at 22:57
1  
well, the 'message' column doesn't help me, BUT the last_outcome_message has all the information I need, including the scheduler name, so I will use it. –  Roni Vered Mar 8 '13 at 13:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.