SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Job Status Report with Error Detail


Job Status Report with Error Detail

Author
Message
royce.bacon
royce.bacon
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 1100
Comments posted to this topic are about the item Job Status Report with Error Detail
Drew Salem
Drew Salem
Mr or Mrs. 500
Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)

Group: General Forum Members
Points: 516 Visits: 172
Great post. I've been meaning to look at how this is done with RS. When I wrote a few articles back in April about using nest stored procs to collect various data, such as failed jobs and their error logs, using the "SCOME" technique, I had a tonne of enquiries on how it could be done using Reporting Services instead of ASP.Net, but I never got round translating them as I was too busy. There was a big and heated debate about why use raw .Net when RS was available. So I used SCOME to create the suite of reports that make up SQLServerMonitor.com to show its flexibility. If you find any of these useful and have the time, maybe you could translate some of these reports to using Reporting Services so I can direct the enquiries to your posts!

Drew
Cameron Mayfield
Cameron Mayfield
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 55
Absolutely Fantatastic information. I will be putting this report together ASAP. You have done an incredible job not only with the creation of the report but in explaining with great detail the steps to replicate. Thanks for a fantastic article and a wonderful idea!
Rob Fisk
Rob Fisk
SSC Eights!
SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)

Group: General Forum Members
Points: 843 Visits: 428
Nice one. Where was this a couple of weeks ago when I was looking to build a job dashboard.

I actually ended up taking things to bits, breaking it down and building it back up in pieces.
I used far more CTEs than I needed but at the time needed to see what was going on.
I actually did the dashboard in PHP because I am just getting to grips with .net and had all the fancy stuff for hover tips and expanding sections already.

It doesn't have a grouped history as with the article though. Just displays all currently active jobs, and looks at the status of their most recent run.

;WITH fullJobHistory AS
(
--Just so I can use a nice date everywhere else
SELECT CAST(CAST(run_date AS VARCHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(run_time AS VARCHAR(6)),6),3,0,':'),6,0,':') AS datetime) AS RunDate,
*
FROM msdb.dbo.sysjobhistory
),
baseJobRecord AS
(
--the top level entries and number them in reverse
SELECT ROW_NUMBER() OVER(PARTITION BY job_id ORDER BY Rundate DESC) AS [jobNumber],
*
FROM fullJobHistory
WHERE step_id=0
)
,
stepFailures AS
(
--get the failure messages for each failed bit
SELECT o.job_id,
o.jobNumber,
f.[message] AS [stepError]
FROM baseJobRecord o
LEFT JOIN baseJobRecord o2
ON o2.job_id = o.job_id
AND o2.jobNumber = o.jobNumber - 1
JOIN fullJobHistory f
ON f.job_id = o.job_id
AND f.step_id > 0
AND f.run_status = 0
AND (o2.instance_id IS NULL
OR f.instance_id BETWEEN o.instance_id AND o2.instance_id)
WHERE o.run_status = 0
AND o.jobNumber = 1
GROUP BY o.job_id,
o.jobNumber,
f.[message]
)
--bung it all together in an output.
SELECT DISTINCT
o.run_status AS [Status],
j.name AS [Job Name],
o.RunDate AS [Last Ran],
--concatenate individual step errors
--using br/hr here since it is for a web tooltip
STUFF((SELECT '<br /><hr />' + CASE
WHEN o2.run_status = 1 THEN NULL
ELSE isnull(s.stepError,o2.[message])
END
FROM baseJobRecord o2
LEFT JOIN stepFailures s
ON s.job_id = o2.job_id
AND s.jobNumber = o2.jobNumber
WHERE o2.job_id = o.job_id
AND o2.jobNumber = o.jobNumber
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,12,'')
AS [Error]
FROM msdb.dbo.sysjobs j
JOIN baseJobRecord o
ON o.job_id = j.job_id
AND o.jobnumber = 1

WHERE j.enabled = 1
ORDER BY 1,2



Will definitely be looking at your Reporting services bits though.

_______________________________________________________
Change is inevitable... Except from a vending machine.

Rommelpos
Rommelpos
Mr or Mrs. 500
Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)

Group: General Forum Members
Points: 533 Visits: 106
Fantastic solution!
Implemented straight away!
Thanks a lot for the useful information.



Rob Fisk
Rob Fisk
SSC Eights!
SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)

Group: General Forum Members
Points: 843 Visits: 428
The one bit in my script that people might find useful in the one from the article is the conversion from the silly* integers to real dates.

The below is actually what you will see in the profiler as EM/SSMS views a job:

CAST(CAST(run_date AS VARCHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(run_time AS VARCHAR(6)),6),3,0,':'),6,0,':') AS datetime) AS RunDate



*OK, so there is the very sensible reason that the way it is stored is very small.

_______________________________________________________
Change is inevitable... Except from a vending machine.

royce.bacon
royce.bacon
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 1100
Drew, I will take a look at those when I get a chance (next year...LOL) and see if I find some of them useful.
Drew Salem
Drew Salem
Mr or Mrs. 500
Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)

Group: General Forum Members
Points: 516 Visits: 172
Fair enough. I kind of of said to everyone I'd look into it, but it seemed a bit silly having to find time to re-engineer the technique to use RS. I'll direct them all to you Smile.
l543123
l543123
SSC-Addicted
SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)

Group: General Forum Members
Points: 492 Visits: 175
nice one.. but why do you need this.. cant we just use email notification when jobs fail and use SSMS - job activity monitor to monitor jobs.
royce.bacon
royce.bacon
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 1100
l543123 (12/15/2009)
nice one.. but why do you need this.. cant we just use email notification when jobs fail and use SSMS - job activity monitor to monitor jobs.


Some of us don't have SSMS installed for various reasons for one thing - like our small shop. Email notices are certainly useful and I use them in conjunction with this report. I find the report gives me a good quick summary of what has run and which jobs have failed and I find that useful. Also this report is sent to others in the group who act as my backup and the email notices aren't always sent to everyone.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search