SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Royce Bacon / Job Status Report with Error Detail / Latest Posts

RE: Job Status Report with Error Detail

Drew Salem — Wed, 16 Dec 2009 15:28:29 GMT

You're absolutely right Terry. Many of the reports do use a user with sysadmin rights for exactly that reason (not just diskspace but others such as the Stopped Agent report that uses xp_servicecontrol to interrogate the status of the SQL Server Agent on each server), but I have the flexibility to do that where I work because of the environment particular to our organisation and the context within which these servers exist. I have just tried other methods such as using the EXECUTE AS clause to grant a user with lesser rights "EXECUTE" to the extended proc. But none work. I'll certainly have a think about it, particularly as I may be moving to a less secure environment with another company. Sorry I couldn't help.

RE: Job Status Report with Error Detail

TerryS — Wed, 16 Dec 2009 13:33:22 GMT

[quote][b]Drew Salem (12/15/2009)[/b][hr][quote][b]TerryS (12/15/2009)[/b][hr]Nice article. This appears to be a single server job monitoring tool. My need would be to do something similar for about 20 SQL servers scattered across 3 domains. Please correct me if I am wrong in my assumption. Thanks.[/quote]The [url=http://en.wikipedia.org/wiki/Scome]SCOME[/url] technique is a very simple way to monitor not just failed jobs but anything SQL Server for an *unlimited* number of SQL Servers. It just uses T-SQL and linked servers. There are various articles here on SQLServerCentral.com that discuss how to set up reports using this method (search for SCOME), along with downloadable scripts. I put up a website, [url=http://www.sqlservermonitor.com]www.sqlservermonitor.com[/url] that hosts a suite of reports that demonstrate this. Every report on this site (Disk space, TLog space, Histories, Failed jobs etc) uses SCOME, only take a few seconds to run across all servers (we have 47) and can be set up using RS if you do not wish to use ASP.Net. It's just a different front end. If you prefered, you don't have to use a front end at all, but just interrogate raw tables with the results. Any questions, please ask.Drew[/quote]Drew,Thanks for your feedback. I, in fact, already do use SCOME for monitoring the status of databases (and by extension the SQL service) and last backup date. Guess I'll just add Failed Jobs (or even status of all jobs as in the original article) to the list. You mentioned monitoring disk space through SCOME. How are you accomplishing that? I used to use xp_fixeddrives with SQL 2000, but since 2005 that stored proc requires sysadmin privileges to run. My SCOME monitoring at this point only uses public and woudl give nothing higher than read (as in your SCOME articles). Thanks.Terry

RE: Job Status Report with Error Detail

BTW — Wed, 16 Dec 2009 10:33:16 GMT

That was it. I just had a brain f**t. Thanks.

RE: Job Status Report with Error Detail

Drew Salem — Wed, 16 Dec 2009 10:30:13 GMT

[quote][b]brett.walker (12/16/2009)[/b][hr]Thanks for the post. I have started implementing it but am having a problem. I seem to be having a problem finding the SP. I am getting a "Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'sp_help_job'." when I run this (sp_help_job @enabled = 1) in Management Studio. I have sa rights but am not sure why I'm having trouble. I am running SQL Server 2008 Standard Ed. I am not a newby at SQL Server but this is making me feel like one. :-)[/quote]Hi,The sp_help_job procedure lives in the msdb database. Do you have the msdb database selected?

RE: Job Status Report with Error Detail

royce.bacon — Wed, 16 Dec 2009 10:29:19 GMT

[quote][b]brett.walker (12/16/2009)[/b][hr]Thanks for the post. I have started implementing it but am having a problem. I seem to be having a problem finding the SP. I am getting a "Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'sp_help_job'." when I run this (sp_help_job @enabled = 1) in Management Studio. I have sa rights but am not sure why I'm having trouble. I am running SQL Server 2008 Standard Ed. I am not a newby at SQL Server but this is making me feel like one. :-)[/quote]The sp_help_job stored procedure is in the msdb database so make sure you are in the msdb database.

RE: Job Status Report with Error Detail

BTW — Wed, 16 Dec 2009 10:13:00 GMT

Thanks for the post. I have started implementing it but am having a problem. I seem to be having a problem finding the SP. I am getting a "Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'sp_help_job'." when I run this (sp_help_job @enabled = 1) in Management Studio. I have sa rights but am not sure why I'm having trouble. I am running SQL Server 2008 Standard Ed. I am not a newby at SQL Server but this is making me feel like one. :-)

RE: Job Status Report with Error Detail

simonl-768581 — Wed, 16 Dec 2009 02:14:53 GMT

[quote][b]PSB1 (12/15/2009)[/b][hr]Really really useful. Most of our jobs are set to move to next step on failure, so I've altered it to report on step failures only rather than the success or failure of the job itself. It's not something I would ever have had the time to investigate and implement from scratch, whereas now it's already deployed and in my Windows startup profile :-)Many thanks!!![/quote]^^^^ What he said :-)

RE: Job Status Report with Error Detail

Drew Salem — Tue, 15 Dec 2009 14:03:02 GMT

[quote][b]TerryS (12/15/2009)[/b][hr]Nice article. This appears to be a single server job monitoring tool. My need would be to do something similar for about 20 SQL servers scattered across 3 domains. Please correct me if I am wrong in my assumption. Thanks.[/quote]The [url=http://en.wikipedia.org/wiki/Scome]SCOME[/url] technique is a very simple way to monitor not just failed jobs but anything SQL Server for an *unlimited* number of SQL Servers. It just uses T-SQL and linked servers. There are various articles here on SQLServerCentral.com that discuss how to set up reports using this method (search for SCOME), along with downloadable scripts. I put up a website, [url=http://www.sqlservermonitor.com]www.sqlservermonitor.com[/url] that hosts a suite of reports that demonstrate this. Every report on this site (Disk space, TLog space, Histories, Failed jobs etc) uses SCOME, only take a few seconds to run across all servers (we have 47) and can be set up using RS if you do not wish to use ASP.Net. It's just a different front end. If you prefered, you don't have to use a front end at all, but just interrogate raw tables with the results. Any questions, please ask.Drew

RE: Job Status Report with Error Detail

royce.bacon — Tue, 15 Dec 2009 12:57:54 GMT

[quote][b]TerryS (12/15/2009)[/b][hr]Nice article. This appears to be a single server job monitoring tool. My need would be to do something similar for about 20 SQL servers scattered across 3 domains. Please correct me if I am wrong in my assumption. Thanks.[/quote]You are correct that it is single server. In my environment, a small shop, I only have two servers with 90+% of the jobs on one of them. And each server happens to have a Report Server on it (2000 vs. 2005). So What I've done is implement this report on each server. For 20 servers that may not be as practical. To do a combined one you might be able to do something with Linked servers and use a UNION of data from each server. I'm not real up on Linked Servers since like I said we don't have a real need for them, but it seems like that might be a route to try.

RE: Job Status Report with Error Detail

TerryS — Tue, 15 Dec 2009 12:41:16 GMT

Nice article. This appears to be a single server job monitoring tool. My need would be to do something similar for about 20 SQL servers scattered across 3 domains. Please correct me if I am wrong in my assumption. Thanks.

RE: Job Status Report with Error Detail

royce.bacon — Tue, 15 Dec 2009 11:06:12 GMT

I'm really glad that many people are finding this useful. This is my first submission here and these kind of results makes me interested in submitting more.

RE: Job Status Report with Error Detail

cmallain — Tue, 15 Dec 2009 10:56:38 GMT

this is how I formatted the time (using msdb.dbo.sysjobs though...). I am fairly new to more advanced SQL so hopefully I didn't waste too much time after seeing that above :crying: ,convert(datetime,case when sjs.active_start_time = '0' then '00:00:00' when sjs.active_start_time < 1000 then '00'+':0'+substring(cast(sjs.active_start_time as varchar(6)),1,1)+':00' when sjs.active_start_time < 10000 then '00'+':'+substring(cast(sjs.active_start_time as varchar(6)),1,2)+':00' when sjs.active_start_time < 100000 then '0'+ substring(cast(sjs.active_start_time as varchar(6)),1,1)+':'+ substring(cast(sjs.active_start_time as varchar(6)),2,2)+':'+ substring(cast(sjs.active_start_time as varchar(6)),4,2) when sjs.active_start_time >= 100000 then substring(cast(sjs.active_start_time as varchar(6)),1,2)+':'+ substring(cast(sjs.active_start_time as varchar(6)),3,2)+':'+ substring(cast(sjs.active_start_time as varchar(6)),5,2) else null end,108) as new_active_start_time--select top 5 * FROM msdb.dbo.sysjobs sjleft outer join msdb.dbo.sysjobschedules sjs on sj.job_id = sjs.job_id and sjs.enabled = 1

RE: Job Status Report with Error Detail

PSB1 — Tue, 15 Dec 2009 09:43:01 GMT

Really really useful. Most of our jobs are set to move to next step on failure, so I've altered it to report on step failures only rather than the success or failure of the job itself. It's not something I would ever have had the time to investigate and implement from scratch, whereas now it's already deployed and in my Windows startup profile :-)Many thanks!!!

RE: Job Status Report with Error Detail

royce.bacon — Tue, 15 Dec 2009 08:40:31 GMT

[quote][b]Gator-221056 (12/15/2009)[/b][hr]A shared datasource with SA credentials on a report server.... what's the worst that could happen? ;)While I understand the simplicity and brevity you were going for for the purposes of this article.... wouldn't it at least make more sense to grab the data with a job, dump into a repository and have the report just use read only credentials on that repository?Just having an account be owner over MSDB didn't give you the data you needed?[/quote]I know that isn't the ideal situation from a security perspective. I tried various alternatives that didn't seem to work and I don't recall all of what I tried at this time. I do have a special account "ReportViewer" that I use for other reports that I was planning to get it, or maybe a different one, to work for this report when I have a chance.

RE: Job Status Report with Error Detail

Gator650 — Tue, 15 Dec 2009 08:30:30 GMT

A shared datasource with SA credentials on a report server.... what's the worst that could happen? ;)While I understand the simplicity and brevity you were going for for the purposes of this article.... wouldn't it at least make more sense to grab the data with a job, dump into a repository and have the report just use read only credentials on that repository?Just having an account be owner over MSDB didn't give you the data you needed?

RE: Job Status Report with Error Detail

alen teplitsky — Tue, 15 Dec 2009 08:16:27 GMT

nice postthere was a similar article a few weeks ago that i extended for the same thing. big difference is i import sysjobs and jobhistory daily for all monitored servers and my report has the reason for the failure in the report.later on i plan to extend it with data like average run time to give a heads up if a job suddenly takes longer to run.and to get around the annoying int to date conversion i added a date column myself

RE: Job Status Report with Error Detail

alen teplitsky — Tue, 15 Dec 2009 08:13:56 GMT

[quote][b]l543123 (12/15/2009)[/b][hr]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.[/quote]i like seeing everything in one email daily or reading it on the website. otherwise you get email clutter.and if i'm in the mood on the train ride to work, i'll read the report on my iphone

RE: Job Status Report with Error Detail

l543123 — Tue, 15 Dec 2009 08:00:11 GMT

makes sense :) thanks for the reply.

RE: Job Status Report with Error Detail

royce.bacon — Tue, 15 Dec 2009 07:55:02 GMT

[quote][b]l543123 (12/15/2009)[/b][hr]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.[/quote]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.

RE: Job Status Report with Error Detail

l543123 — Tue, 15 Dec 2009 07:46:34 GMT

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.

RE: Job Status Report with Error Detail

Drew Salem — Tue, 15 Dec 2009 07:22:41 GMT

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 :).

RE: Job Status Report with Error Detail

royce.bacon — Tue, 15 Dec 2009 06:57:45 GMT

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.

RE: Job Status Report with Error Detail

Rob Fisk — Tue, 15 Dec 2009 06:47:47 GMT

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:[code="sql"]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[/code]*OK, so there is the very sensible reason that the way it is stored is very small.

RE: Job Status Report with Error Detail

Runica — Tue, 15 Dec 2009 06:18:55 GMT

Fantastic solution!Implemented straight away!Thanks a lot for the useful information.

RE: Job Status Report with Error Detail

Rob Fisk — Tue, 15 Dec 2009 04:56:50 GMT

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.[code="sql"];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 jJOIN baseJobRecord o ON o.job_id = j.job_id AND o.jobnumber = 1WHERE j.enabled = 1ORDER BY 1,2[/code]Will definitely be looking at your Reporting services bits though.

RE: Job Status Report with Error Detail

Cameron Mayfield — Tue, 15 Dec 2009 04:21:05 GMT

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!

RE: Job Status Report with Error Detail

Drew Salem — Tue, 15 Dec 2009 03:41:54 GMT

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 [url=http://www.sqlservermonitor.com]SQLServerMonitor.com[/url] 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

Job Status Report with Error Detail

royce.bacon — Tue, 15 Dec 2009 00:06:13 GMT

Comments posted to this topic are about the item [B]Job Status Report with Error Detail[/B]