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 2:03 PM


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
TerryS (12/15/2009)
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.


The SCOME 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, www.sqlservermonitor.com 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
Post #834749
Posted Wednesday, December 16, 2009 2:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 5, 2012 4:15 PM
Points: 33, Visits: 45
PSB1 (12/15/2009)
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!!!


^^^^ What he said
Post #834910
Posted Wednesday, December 16, 2009 10:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 16, 2010 1:29 PM
Points: 11, Visits: 28
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 1
Could 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.
Post #835294
Posted Wednesday, December 16, 2009 10:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:36 PM
Points: 37, Visits: 754
brett.walker (12/16/2009)
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 1
Could 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.

The sp_help_job stored procedure is in the msdb database so make sure you are in the msdb database.
Post #835305
Posted Wednesday, December 16, 2009 10:30 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
brett.walker (12/16/2009)
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 1
Could 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.


Hi,

The sp_help_job procedure lives in the msdb database. Do you have the msdb database selected?
Post #835306
Posted Wednesday, December 16, 2009 10:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 16, 2010 1:29 PM
Points: 11, Visits: 28
That was it. I just had a brain f**t. Thanks.
Post #835307
Posted Wednesday, December 16, 2009 1:33 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 2:37 PM
Points: 881, Visits: 1,008
Drew Salem (12/15/2009)
TerryS (12/15/2009)
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.


The SCOME 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, www.sqlservermonitor.com 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


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



Post #835417
Posted Wednesday, December 16, 2009 3:28 PM


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
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.
Post #835472
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse