December 15, 2009 at 9:43 am
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!!!
December 15, 2009 at 10:56 am
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 sj
left outer join msdb.dbo.sysjobschedules sjs
on sj.job_id = sjs.job_id
and sjs.enabled = 1
December 15, 2009 at 11:06 am
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.
December 15, 2009 at 12:41 pm
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.
December 15, 2009 at 12:57 pm
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.
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.
December 15, 2009 at 2:03 pm
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
December 16, 2009 at 2:14 am
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 🙂
December 16, 2009 at 10:13 am
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. 🙂
December 16, 2009 at 10:29 am
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 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. 🙂
The sp_help_job stored procedure is in the msdb database so make sure you are in the msdb database.
December 16, 2009 at 10:30 am
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 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. 🙂
Hi,
The sp_help_job procedure lives in the msdb database. Do you have the msdb database selected?
December 16, 2009 at 10:33 am
That was it. I just had a brain f**t. Thanks.
December 16, 2009 at 1:33 pm
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
December 16, 2009 at 3:28 pm
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.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply