Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Job Status Report with Error Detail Expand / Collapse
Author
Message
Posted Tuesday, December 15, 2009 12:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 38, Visits: 763
Comments posted to this topic are about the item Job Status Report with Error Detail
Post #834321
Posted Tuesday, December 15, 2009 3:41 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, January 12, 2014 9:26 PM
Points: 68, 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
Post #834379
Posted Tuesday, December 15, 2009 4:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 4, 2010 1:08 PM
Points: 3, 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!
Post #834396
Posted Tuesday, December 15, 2009 4:56 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2014 8:32 AM
Points: 163, 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.
Post #834411
Posted Tuesday, December 15, 2009 6:18 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, October 17, 2014 3:24 AM
Points: 515, Visits: 82
Fantastic solution!
Implemented straight away!
Thanks a lot for the useful information.



Post #834434
Posted Tuesday, December 15, 2009 6:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2014 8:32 AM
Points: 163, 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.
Post #834450
Posted Tuesday, December 15, 2009 6:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 38, Visits: 763
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.
Post #834461
Posted Tuesday, December 15, 2009 7:22 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, January 12, 2014 9:26 PM
Points: 68, 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 :).
Post #834483
Posted Tuesday, December 15, 2009 7:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 1, 2014 11:34 AM
Points: 42, Visits: 174
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.
Post #834509
Posted Tuesday, December 15, 2009 7:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 38, Visits: 763
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.

Post #834517
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse