SQLServerCentral / Discuss content posted by tjaybelt / Article Discussions / Article Discussions by Author / A Failed Jobs Monitoring System / Latest Posts

RE: A Failed Jobs Monitoring System

SQL Pete — Mon, 17 Jan 2011 04:19:04 GMT

I implemented a very similar solution a while back and find it works well. I also have a SSRS front end to view my data. The monitoring solution also grabs data from all databases, last backup date/time etc. It also keeps an inventory with version numbers, collation etc etc.I have also managed to get disk usage too, though the process is a little [i]dirty[/i].I did it because I want complete control of what I monitor/report and I too didn't trust SQL Server Mail, though db mail is much beter these days.

RE: A Failed Jobs Monitoring System

michael.morse — Wed, 16 Jun 2010 12:14:33 GMT

I was trying to test this solution out but I get an error stating I am missing the sp_SMTPMail object. Any chance you could script the one you have? I have tried to search online but non of the examples provide the @query parameter. Thank you.

RE: A Failed Jobs Monitoring System

alen teplitsky — Mon, 23 Nov 2009 09:04:11 GMT

great article i just foundin my case i can't use linked servers in all cases to monitor jobs so i'm going to change the code to use SSIS and maybe dump all the data into a central database. we don't have a central monitoring sql server and we're going to use a production server with a separate database to monitor this

RE: A Failed Jobs Monitoring System

Rudy Panigas — Tue, 08 Sep 2009 06:56:38 GMT

Hello everyone,Please ignore my last comment as it was meant for another forum...Opps!See DBAs are not always right :)But to comment on this forum, if you create a loop that would cycle thru the connection to a linked server you could then add any linked failure to a table for review later and therefore you could continue to link to the remaining servers.Rudy

RE: A Failed Jobs Monitoring System

Rudy Panigas — Tue, 08 Sep 2009 06:51:53 GMT

The code provide is not perfect, as everyone has there own idea on "perfect" code, but it does try to link to a server and if not able to connect, it produces an error and continues onto the next server for its link connection. If not, I will gladly update the code as I have continued to may changes to it.The intent here was to show that there are may ways to get the job done and that it isn't necessary to always purchase software. Although we do use tools from Quest and RedGate to help on our duties, sometimes times the in-house developed tool does the trick. As anyone can tell you each company's network has its own characteristics thus needing a personalized touch.Glad to see that many people have at least looked at this article and have posted a reply.RudyPS. Please remember that I never said that this was a perfect solution and if you think you can create something better, then do so and post it at ServerCentral.com. We would love to see it :)

RE: A Failed Jobs Monitoring System

Chris Howarth-536003 — Sat, 05 Sep 2009 03:17:50 GMT

[quote][b]Mark Jones (9/4/2009)[/b][hr]Good solution, but in a world of non perfect trusted domains the linked server solution simply has too many issues to be viabale. Because you only open a single process within a stored procedure that runs agaginst all servers in your set, if a problem is encountered running remote SQL through linked servers the entire process will fail and end. for example:If you have 10 servers you will query if server 2 fails, then you will miss the remaining 8 servers.[/quote]Not necessarily, you could use sp_testlinkedserver and a bit of error handling to trap any connectivity issues before firing any queries at the linked server.http://msdn.microsoft.com/en-us/library/ms189809.aspxPersonally I prefer an Integration Services approach, using a package variable populated from a lookup table to loop through and connect to each server in turn.Chris

RE: A Failed Jobs Monitoring System

Fred Panos — Fri, 04 Sep 2009 09:01:38 GMT

One thing we have done in addition to the above is to create a web page that lists the job results and then have a 3rd Party alert system check that web page for key words. Then if it finds it can email (which is redundant) but it can also automatically call someone. The monitor we use is called AlertSite and it can call and progressively call more and more people if the job isn't being resolved. This was crucial for us given that jobs needed to finish at night and we didn't want to have a 24/7 staff presence for the failures.

RE: A Failed Jobs Monitoring System

Jack Corbett — Fri, 04 Sep 2009 07:40:39 GMT

Mark,Good point, but enabling xp_cmdshell once again expands the surface area of your SQL Server. Which one is the lesser of 2 evils?

RE: A Failed Jobs Monitoring System

Mark.L.Jones — Fri, 04 Sep 2009 07:17:28 GMT

Good solution, but in a world of non perfect trusted domains the linked server solution simply has too many issues to be viabale. Because you only open a single process within a stored procedure that runs agaginst all servers in your set, if a problem is encountered running remote SQL through linked servers the entire process will fail and end. for example:If you have 10 servers you will query if server 2 fails, then you will miss the remaining 8 servers.You can get around this by using SQLCMD to run remote SQL queries rather than linked servers. This opens new process to every server you wish to connect to, SQLCMD is easily runable from a TSQL query by using xp_cmdshell, port the results into a table by using an INSERT command embedded in your remote sql statement.

RE: A Failed Jobs Monitoring System

darryl_marshall — Fri, 04 Sep 2009 01:02:18 GMT

We use a similar system to this to monitor all our servers. Had a look at Idera SQL Job Manager but it ran very slowly on my machine and we needed more information about other aspects of the servers than just the jobs. At the moment lots of details on disk space, db size, job outcomes and a number of logging tables are pulled back to a central server for reporting against. SQL Reporting service is then used to report against the data and send out email reports as needed.

RE: A Failed Jobs Monitoring System

Rudy Panigas — Mon, 15 Jun 2009 09:10:21 GMT

Using MOM. Yes this is a good tool but not everyone has the budgets to get MOM. This is the situation at my location, although there would be money available now, but I think this is working much better.We use this tool/setup to also collect additional information from not just SQL servers but also from Oracle servers and MySQL servers as well. Not certain if MOM does Oracle/MySQL but so far this setup is working well.Just go to show that there are many ways to get things done and you can bet a DBA/Develop will figure something out.Thanks for the comments,Rudy

RE: A Failed Jobs Monitoring System

FreeHansje — Mon, 15 Jun 2009 07:22:11 GMT

[b]If you're already setting up a separate server, why not use MOM?[/b]I've looked into that and cannot find a rule to monitor failed jobs. If I have to build this rule myself, then why not go for a tailormade solution anyway?[b]Personally, this seems like a lot of heavy lifting and manual labor, and the effort involved seems to outweigh the costs of commercial tools already available.[/b]This is correct, however, have you even looked into the extra load some of these tools add to a server? Dunno about Sentry's Eventmanager, but others where a firm no-no after a day or so of testing. Idera JM was 1 of those.[b]For job monitoring, I just get each server to send an email (per job) when a job fails.[/b]Yes, nice 1, we have more then a 1000 jobs running all over the place, and alas, not only the DBA can go there and add/delete jobs. I prefer a central monitoring system.However, I miss the job to run this setup. I can figure it out, but do I miss something here? It's not part of the download?

RE: A Failed Jobs Monitoring System

sqldba-294117 — Sat, 23 Feb 2008 16:22:57 GMT

I came across this site, so i thought let me share with forum.SQL job managerView and manage SQL Server Jobs [b]Free for a limited time[/b]http://www.idera.com/Products/SQLjobmanager/

RE: A Failed Jobs Monitoring System

stew_b — Tue, 12 Feb 2008 08:48:11 GMT

Great idea Scott - Thank you!

RE: A Failed Jobs Monitoring System

Strommy — Mon, 11 Feb 2008 15:55:03 GMT

Good points about the job failure logic. In general, if we ever have a job that fails and then sends a failure notification, we have that job step exit failed, regardless of success or failure of that step. As an aside, if we have a ticketing system monitoring job failures, we don't need the notification step. That was part of our goals for integrating with NetIQ.Thanks,Eric

RE: A Failed Jobs Monitoring System

The CosmicTrickster — Mon, 11 Feb 2008 14:52:26 GMT

@Strommy: In my last job we used NetIQ. A lot easier than Tivoli. You just have to watch out for adjusting child jobs - any changes to the parent job will overwrite it. A gotcha for people who don't watch what view they are in on the console.@stew_b: A possible solution is to have a step between each to handle failures. Like so:Step 1: Step1Tasks onsuccess goto step 3, onfail goto nextStep 2: Report failure of step 1 onsuccess goto next, onfail goto nextStep 3: Step3Tasks onsuccess quit with success, onfail goto nextStep 4: Report failure of step 3 onsuccess quit with success, onfail quit with failIt depends what your steps are doing and whether you can incorporate error-reporting within them (e.g. RAISERROR). Failing that, use the above structure with e.g. RAISERROR('Step x failed', 16, 1) WITH LOG and check the ERRORLOG (manually or automate it) or get it to send an email (you may get spammed, watch out for that!).

RE: A Failed Jobs Monitoring System

tjaybelt — Fri, 08 Feb 2008 12:48:53 GMT

that is the next step. its difficult to gather specific info fom the job steps, cause they dont key into the job, just seems they are ordered by time, and linked to a job... so the organization is a bit lacking. step 0 is the job report. other steps could fail. But finding the specific message that failed, and specific step, may be difficult, if the job failes a lot of times in a row...Im struggling with piecing this together now.

RE: A Failed Jobs Monitoring System

Strommy — Fri, 08 Feb 2008 11:48:27 GMT

Interesting solutions. For large environments, enterprise monitoring software is very useful. Our environment has 200+ SQL instances and we use NetIQ. We finished up a project that monitors all types of problems and will create a service ticket based on the monitoring events. The best part is the set-up for new servers. Install the client, add the server to the proper monitoring group in NetIQ, and we can start getting monitoring events from SQL (including failed and long-running jobs).Thanks,Eric

RE: A Failed Jobs Monitoring System

stew_b — Fri, 08 Feb 2008 11:17:45 GMT

This is a great article! How can I take it to the next level - I have a multistep job that is executing some but not all of the steps. The SQL logs/tables report job success, but I suspect now it is become at least SOME part of the job succeeded. Without parsing each step out into seperate jobs, is there way to get detailed information about the success or failure of each step within the job?

RE: A Failed Jobs Monitoring System

Peter Waldweben — Thu, 07 Feb 2008 09:22:53 GMT

Nice solution! However I got a message that sp_SQLSMTPmail does not exist. Downloaded it somewhere. Next error about '@vc parameter that does not match with this procedure'. What am I doing wrong? Thanks in advance. Peter

RE: A Failed Jobs Monitoring System

Rudy Panigas — Thu, 07 Feb 2008 07:02:53 GMT

I agree. I only monitor of the exceptions/failures. Don't need to know that a job ran successfully. But I have other reports that should all jobs successful or not. In the morning I just review the failed ones. Have an email alert don't really help me as we are not a 24x7 shop so the web page report (with SSRS) works great.Rudy

RE: A Failed Jobs Monitoring System

The CosmicTrickster — Wed, 06 Feb 2008 15:25:08 GMT

[quote][b]Timothy Ford (2/6/2008)[/b][hr]Scott, do you really want an email every time a job fails on every SQL instance? Must have a lot of free time on your hands and space in your in-box. Avg. 20 jobs per instance * 80 instances * running N times per day = WHOA![/quote]That's why I monitor by exception. :DI have very few job failures. What generally causes something to fail? Something changing. The systems are locked down and we have a rigorous (and improving) change process. For things like disk space, that's being monitored (DB growth rates as well) and flagged before backups & the like fail.Granted, our systems are relatively straightforward as well, no complex replication scenarios (we do have replication), no flaky network links.This may also change once we get a better centralised monitoring tool in place, where alerts can be sent to a console. Our current monitoring software doesn't handle that so well.

RE: A Failed Jobs Monitoring System

tjaybelt — Wed, 06 Feb 2008 13:54:34 GMT

I agree that writing it yourself vs buying software is a compelling argument, and people are on both sides of the camp. In my case, the buy it option was not an option. ever. Unfortunately.So, instead of doing it in a lot more manual process, this is the solution that allows me to sit back and do other things, while i feel safe that the jobs are being monitored. Even if it cost a lot more for me to develop. And thats part of the reason i am sharing it. Why should you reinvent the wheel i just reinvented. Unless its for the self education on the process. Which was another selfish reason for writing it. Loads of baggage went into the system, and i just wanna share it. I love that there have been so many different ideas shared here on this topic. its one close to my heart, and i love that so many options are out there. hopefully people will be albe to pick the best one for their shop.

RE: A Failed Jobs Monitoring System

Timothy Ford-473880 — Wed, 06 Feb 2008 13:43:27 GMT

Scott, do you really want an email every time a job fails on every SQL instance? Must have a lot of free time on your hands and space in your in-box. Avg. 20 jobs per instance * 80 instances * running N times per day = WHOA!

RE: A Failed Jobs Monitoring System

The CosmicTrickster — Wed, 06 Feb 2008 13:37:45 GMT

For job monitoring, I just get each server to send an email (per job) when a job fails. DBMail on SQL205, SMTP stored proc for 2000.As far as auditing goes, if you have a Service Desk, log an Incident for each job failure (or add an entry to an existing incident if it is a work in progress sort of thing) and put the resolution in there. You should be able to search for any historical incidents relating to a particular server easily enough. Having some sort of known problem repository helps as well (especially for those failures that only happen every 6 months or so).

RE: A Failed Jobs Monitoring System

Rudy Panigas — Wed, 06 Feb 2008 08:02:49 GMT

The reason for me to pull the information instead of pushing is basically that new servers can be added to the network and I don't have to install or setup anything on that server. By updating the table on my repository server, it will then automatically connect to it.Nice to see all the different ideas. Different is good as long as it makes your job easier and better.Rudy

RE: A Failed Jobs Monitoring System

DrewTheEngineer — Tue, 05 Feb 2008 16:30:43 GMT

I feel you pain ... we have over 200 SQL Server 2000 instances that need to be monitored, and not just for SQL job failures.The solution I went with works somewhat in the opposite direction as yours. Instead of setting up a repository server with linked servers to all the instances, I set up all the instances with a linked server to the repository server. I then configured all the instances as target servers to a master MSX server. The MSX server pushes out a SQL job to all the target servers and this SQL job collects data (including job history) and feeds it into the repository server. The repository server then processes the collected data and sends notifications as necessary. To make sure that all the target servers are actually sending their data to the repository server, they all update a time stamp as part of their collected data. I can then query the time stamps to make sure all the target servers are sending over thier data.

RE: A Failed Jobs Monitoring System

aaron.bertrand — Tue, 05 Feb 2008 16:07:22 GMT

Yes, it's true, not every tool will do everything you want. That is true of pretty much all software out there, whether you bought it, wrote it, bought the source code and adapted it, etc.However I use Event Manager on all of my instances and there is nothing that I have needed to date that it doesn't do.Also, analyzing SQL Server Error Logs seems to be a bit of a disparate process from being told that a job failed, and I don't know that it is something you should expect from a tool dedicated to monitoring SQL Server Agent. It sounds like something more along the lines of a SQL Server error monitor tool.As for the $2,000 figure, well, you can certainly get tools like Event Manager for less than $2,000 per instance. And what I hate about these debates is that managers and even some IT folks and DBAs think that their time spent developing software is free. Surely the amount of time you spent writing your code was worth well more than $2,000 in opportunity cost that you could have devoted to other tasks, and so you have not saved any money really, just shifted the line item on the income statement from "3rd party software" to "employee salary." Especially if you end up charging overtime or off-hours compensation because you couldn't get your normal work done while you were re-inventing the wheel. :-)

RE: A Failed Jobs Monitoring System

Rudy Panigas — Tue, 05 Feb 2008 14:14:56 GMT

I agree that there are tools out there that can do most of the work. I say most because there is always something that the tool does not cover. For example my reporting system also analyzes the SQL error logs (my report show the errors that I need to look into or can use to troubleshoot) but apps I've seen don't. If all the apps did everything we need, then most of this site would not be needed. Just look at all the great scripts, process improvements and articles!The other aspect is dollars. Not every DBA can spend $2,000.00 dollars per instance or per server to buy ABC program to help out. Generally they have a very limited budget and have to spend the money wisely.I use several purchased tools to help my work (not going to mention them as I'm not in sales) but there is alway things that are custom to your company and you have to figure out a way to automate or at least change their process to make it easier on the DBA.Rudy

RE: A Failed Jobs Monitoring System

aaron.bertrand — Tue, 05 Feb 2008 13:34:53 GMT

Personally, this seems like a lot of heavy lifting and manual labor, and the effort involved seems to outweigh the costs of commercial tools already available. For example, SQL Sentry's Event Manager is a very affordable option, and it does everything your solution does and more... including elaborate event notifications not only for failed jobs but also job runtime thresholds, event chaining and a very nifty Outlook-style calendar view that gives you a graphical view of your job schedules across many instances.I'm usually a total advocate for reinventing the wheel, if you are going to add something very powerful that isn't already available in packaged solutions (or if you really want to learn the API, catalog views, DMVs, Agent subsystem, etc). In this case I suggest your readers take trial versions of available packages for a spin before going too far down the "roll-your-own" road. At the very least you will see what you might want (and might not want) when you build your own solution, but more often than not you will realize how complex it can be to go that route, and that you will actually save money by spending money on a ready-made tool.This is a classic argument that I have been having for ages. Back when ASP was a popular web language I used to argue until I was blue in the face with people who wanted to write their own mail or upload component, to save the $99 or $150 of the premier such component already available to buy on the spot. For most of us, if it takes us more than hour to build such a component, we're already behind. Then there is testing, debugging, performance testing, etc. etc. All of which you get for free when you have a reputable vendor behind the product.

RE: A Failed Jobs Monitoring System

Rudy Panigas — Tue, 05 Feb 2008 13:18:16 GMT

Good article. I too have created a Job Failure management system that easier to use as it uses linked servers along with a reports created in SSRS. It's funny how no vendor has created an application for DBAs to manage multiple servers easily and effectively. My company has over 100 instances on 80 servers that I manage..alone.Just my 2 cents worth,Rudy

RE: A Failed Jobs Monitoring System

Timothy Ford-473880 — Tue, 05 Feb 2008 12:36:42 GMT

First of All: Nice Job TJay.I too built a similar system using linked servers in order to monitor failed jobs, backup history/backups out of date tollerance, databases::applications::servers, space issues at the file level, etc. What I've moved onto in SQL 2005 is a similar system, with the same Reporting Services reports as I was using prior (and practically the same schema) using SSIS. You can populate a table with your server/instance names and so long as you keep that up to date as you bring new instances online you're good-to-go. It was based in-part off of an article in [u]SQL Server Magazine[/u] in May or June of 2007 on SSIS. It runs seamless against my 80+ instances and 800+ databases with much less overhead than my old linked server solution. I've only had it fail once, but that was due to an instance being down for maintenance. With a little more work on the customized logging that would have been apparent however.

RE: A Failed Jobs Monitoring System

bcb — Tue, 05 Feb 2008 12:02:01 GMT

I've been using a job failure email notification method for some years now on SQL 2000 with the following differences:1) I don't use a monitor server to check the other servers, but rather just include a final email failure step on each job, only executing this step if a previous step fails. That way I avoid the linked server problems mentioned in the article and I keep it simple. (The one disadvantage is if the server goes down, no email gets sent; whereas a central monitoring server solution can catch this. But our operations unit knows within seconds if a server goes down, so not a problem for me.)2) On my SQL 2000 servers, I don't use SQLMail because a) it requires a MAPI client and b) it's flaky (often started failing after months of no problems). I replaced it with an smtp solution developed by Gert Drapers (formerly a software architect for Microsoft) called xp_smtp_sendmail. I've used it for years and it is very robust, has never failed once.

RE: A Failed Jobs Monitoring System

isaacmjohnson — Tue, 05 Feb 2008 08:40:12 GMT

I am not sure if this method has already been shared because I have not figured out how to go to the the beginning of the thread (new user)! Anyway, I am not a true DBA but I am a manager of system operations. I developed a proactive method for alerting me when a job fails by simply creating triggers on the sysjobhistory table and then emailing me the log output that was generated by the job (provided I enabled the job to [i]produce[/i] a log. It saved me time and loads of money messing around with 3rd party products or figuring out how to get other components of MS SQL 2005 to work how I wanted them to work. Obviously, db_sendmail needs to be enabled on each server that you are planning on deploying the solution to (or if you use some kind of linked server setup, just enable it on one server). I advise that anyone considering this solution check with their company's security practices when deciding for or against enabling mail on the database server.

RE: A Failed Jobs Monitoring System

Simon Facer — Tue, 05 Feb 2008 08:32:39 GMT

I came up with a similar solution. Except I have a proc that creates Linked Servers using a single name (LS_Q), before executing the queries, this makes the subsequent coding easier - no dynamic SQL to deal with, and easier to expand the processing - so far I have expanded my system to include SQL File statistics (Size / Used) for all MDF, NDF and LDF files, some basic IO stats, Failed Jobs, Current Backups, etc.The code could have been better written - not easy to read / understand (SQL Refactor is good for this if you are an untidy coder, like most of us).

RE: A Failed Jobs Monitoring System

ghardy — Tue, 05 Feb 2008 08:27:08 GMT

If you're already setting up a separate server, why not use MOM? You then get the other benefits of MOM like other SQL Server monitoring and monitoring of the servers besides failed jobs. We monitor databases for close to 200 SQL Server applications and MOM works great. MOM automatically installs it's client on a server when it detects a new SQL Server installation and starts monitoring. It works great for both SQL 2000 and 2005.

RE: A Failed Jobs Monitoring System

grahamc — Tue, 05 Feb 2008 07:32:02 GMT

We do employ a very similar method of managing our SQL servers. We have about 30 or so SQ Servers and then most of those are replicated through multiple development, test and hotfix (virtual) environments. We have a large number of SQL servers to maintain and products like SQL Stripes become very expensive. Only just finishing off the setup, but having a few issues with the distributed transactions, but its looking good so far.Graham

RE: A Failed Jobs Monitoring System

Scott Arendt — Tue, 05 Feb 2008 07:27:46 GMT

I too use an underscore when naming a job. I started this practice after upgrading my workstation to SQL Server 2005. SQL Management Studio sorts jobs differently than Enterprise Manager. I got tired of scrolling to the bottom of the list to see my jobs, so I started adding a underscore to make life easier.

A Failed Jobs Monitoring System

tjaybelt — Mon, 04 Feb 2008 21:56:18 GMT

Comments posted to this topic are about the item [B]A Failed Jobs Monitoring System[/B]