Monitoring - How to Alert The DBA when the Failure is at the Server level

  • SQL Server Jobs provide notification options at the Job level for when a Job fails and or completes so that if a job start and fails one or more person can be notified.

    However what method or process do you have in place to notify you when the failure occurs because the SQL Server Agent is not running and or the SQL Service itself is off line? How do you know when a scheduled job has not run because SQL Agent or SQL Server were not running at the timne your job was set to start?

    Without getting to long winded (I have re-written this several times already only to end up being verbose), we had a power outage this past weekend that was long enough that a major SQL Job I had scheduled did not run and because the server was off line and did not come back on till after the scheduled time, SQL Server did not notify me that the job had failed.

    I realie I can actively check SQL Server to see if a job has run but I have a number of after hours and weekend jobs that run and to check each one manully every time it's scheduled is being just a bit unreasonable. Its important to know if a scheduled job has failed so that I can manually re-schedule it and soon because the failure of a couple of these jobs can have a significant impact on performance during the workday. And if you ahve to run a job at night because fo it's duration and or what it does then if it fails on Saturday then you have onloy 1 more night to run it before the work week starts up again.

    I've setup an Job that does nothing but emaile me when it runs and it is scheduled to run at the start up of SQL Server Agent. This allows me to get email notification anytime the system reboots and of course whenever the SQL Serverice re-starts. While this works it has some drawbacks to it and so I'd like to hear what the rest of you do to handle tehse kinds of potential failures with SQL Server.

    Kindest Regards,

    Just say No to Facebook!
  • Our NT group monitor's the status of services. If a service enters the stopped status, we are alerted.

    /* ----------------------------- */
    Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

  • One of our corporate teams monitors that sort of thing for us (Server is offsite) and notified us of these problems. Some alerts are auto, some are manual.

    Our cluster service is set up to send us email notifications when the cluster fails, too.

    Lastly, I've created a manual T-SQL job that monitors the job log tables and tells us every morning what jobs have failed. You could create something similar to email you a list of jobs that haven't been run in the last X hours.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin (4/30/2010)


    One of our corporate teams monitors that sort of thing for us (Server is offsite) and notified us of these problems. Some alerts are auto, some are manual.

    Our cluster service is set up to send us email notifications when the cluster fails, too.

    Lastly, I've created a manual T-SQL job that monitors the job log tables and tells us every morning what jobs have failed. You could create something similar to email you a list of jobs that haven't been run in the last X hours.

    Excellent suggestion Brandie, Thanks.

    BTW - What I have done so far is to setup a job that run on SQL Agent restart that looks at the defined schedule of reboots/restarts and if one occurs outside of the schedueld time by more then 15 minutes it will email an alert to the operator we use for after hours alerts.

    Kindest Regards,

    Just say No to Facebook!
  • Glad I could help. Sounds like you're on the right track for your job.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 5 posts - 1 through 4 (of 4 total)

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