Post reply

Automatic Job Monitoring

  • July 15, 2006 at 3:20 am

    #61931

    I'm a SQL Server 7/2000 DBA and manages about 40 servers in different networks. Every morning I check through the Enterprise Manager if all Jobs (backup, maintenance, etc.) have run successfully. This check costs me 1 hour per day.

    Because of a reorganization I've got some new college's and lost some college's. My new college's think this is to much work, so it should be automated. They only want the failed jobs to report an error on a website or something like that, and don't want to check 40 servers. I don't agree in this, because I'm affraid I'm going to miss some errors.

    How do you do your checks every morning?

    Thanks,

    Marco

  • July 15, 2006 at 2:03 pm

    #649413

    I can strongly suggest you MS MOM utility. http://www.microsoft.com/mom/default.mspx

    It can notify you for failed jobs as well as any other error you may want to be notified. (Hardware errors, disk problems etc.) I use it for about 10 SQL Servers and happy with it so far

  • July 17, 2006 at 2:25 am

    #649471

    If you have slow connections to your remote servers I suggest creating a web page that reads data from a table located on a local server. Every time a job succedes it goes on a step that updates a datetime column with a getdate() value on the local table using linked server conection. That way we know when a job didn't succeded or never run because it didn't wrote the time of completion.

    On our company we seccessfully monitor this way jobs (including backup, replication) on 200 remote servers on a 128Kb line.

  • July 17, 2006 at 7:16 am

    #649508

    I created my own job monitoring script by modifying a similar script I found on this site. I basically created a table to hold all of the job information from my 5 servers, and I have two stored procedures that collect and email the job information every mornining before I come in. This has saved me an average of 30 minutes a day. I'd be happy to post the scripts if you'd like to see them.

  • July 17, 2006 at 9:10 am

    #649584

    A long time ago, we had something similar to what you are looking for.  We set up a table and had the jobs update it when they started and when they completed with their status.  Then we displayed the table in a web page with red, yellow and green lights.  Red = job failed, yellow = job in progress, green = job compleded successfully.  you have to put in some extra logic to see if a "running" job actually didn't get blown out of the water.  We gave it a generous time to run in and changed it to red in the ASP if it exceeded that amount. 

    users loved it because it was simple and easy to use, they just pull up the web page and scroll to their section.

  • July 17, 2006 at 12:04 pm

    #649641

    you can set up an email to be sent if a job fails

  • July 17, 2006 at 2:01 pm

    #649666

    we collect data about failed jobs and displayed them in html report ( updated daily). It will take just couple of minutes to look into that then.

  • July 18, 2006 at 5:19 am

    #649746

    My take is that it's entirely up to if you want spend an hour every morning looking through jobs in Enterprise manager. If you're not putting anything else on hold while you spend that hour and its something you want to do then go right ahead and continue doing it.

    However, if you have other matters to attend to, then take some of the suggestions already posted and build some sort of summary display. This will turn the 60 minute job in a 5 minute job that a junior can do while you're still enjoying your morning coffee

    Personally, we have MSX/TSX servers setup and all jobs are pushed out from the MSX. That way the job status is shown on the MSX in a single view. I've even put together a little ASP.Net app that the IT Helpdesk guys can check job status without going anywhere near Enterprise Manager.

     

    --------------------
    Colt 45 - the original point and click interface

  • July 18, 2006 at 9:56 am

    #649854

    We use 2 things. MOM and an MSX/TSX server setup. The MSX/TSX server setup has a multiserver job that checks the msdb status of all jobs and emails the error(s) (the first 128 characters of each by job) to me every morning.

    This enables me to monitor 20+ servers, 200+ databases for 1500 users all whilst I read the paper and have my morning coffee (as Phil suggests).

    By the way, I've even cobbled together some minor automation and monitoring for Oracle and UDB2 on my MSX server as well !!!

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • July 18, 2006 at 11:05 am

    #649890

    My job sends me an email about job status on other servers whilst I eat lunch. It is more convenient than during the morning coffee time or five-o'clock tea time.

    Regards,Yelena Varsha

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply