SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Failed Jobs Monitoring System


A Failed Jobs Monitoring System

Author
Message
tjaybelt
tjaybelt
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1223 Visits: 470
Comments posted to this topic are about the item A Failed Jobs Monitoring System



Scott Arendt
Scott Arendt
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5128 Visits: 1733
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.
grahamc
grahamc
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2636 Visits: 1039
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
gsqladmin
gsqladmin
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 384
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.



Simon Facer
Simon Facer
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3245 Visits: 724
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).



isaacmjohnson
isaacmjohnson
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 9
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 produce 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.
bcb
bcb
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 107
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.



Timothy Ford-473880
Timothy Ford-473880
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2277 Visits: 446
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 SQL Server Magazine 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.

- Tim Ford, SQL Server MVP
http://www.sqlcruise.com
http://www.thesqlagentman.com
http://www.linkedin.com/in/timothyford
Rudy Panigas
Rudy Panigas
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4346 Visits: 1325
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



aaron.bertrand
aaron.bertrand
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 707
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search