Post reply

SQL Alerts

  • September 8, 2008 at 1:55 pm

    #123503

    HI,

    Here is a scenario, SQL server Agent service was not running since last couple of days in one of our production servers. And therefore no scheduled job run in these days and no one noticed that. So is there any way to send an alert or an email when any of sql server service gets down?

    Thanks,

    usman

  • September 8, 2008 at 2:07 pm

    #868713

    I was using this approach:

    http://www.databasejournal.com/features/mssql/article.php/10894_3709441_1

  • September 8, 2008 at 2:11 pm

    #868715

    I wrote a little .NET application that accepts a paramter message and sends emails with that subject/message. Then in Windows, Control Panel, Services.. you can set the REcovery option of the service to run a program (that .net app) to run and pass a paramter. This would send notification if the service stops. I usually set it to restart, and on second/third failure send the notice.

    There are also tools and programs. You can write your own service and install it on the machine to check continually if the service is up.

    You can also setup a WMI style query to check the state of a service and make sure its started, but if you run it via agent and agent stops.. .you wont get the notice. Thats why I went the application called by the services control method.

  • September 8, 2008 at 2:15 pm

    #868718

    Thanks guys,

    dmc can you please share that .net application code?

  • September 8, 2008 at 2:43 pm

    #868730

    Sorry for the delay.. due to company policy I can not provide you with the exact code we use. However, I wrote a real quick example that will work and show you the basics of a console application to send the notice. You just need to fill in the from/to/mail server etc values. You could use params if you wanted on these to make it more dynamic and allow sending by service to different people.

    I also have just a generic exception handler, you might want to write to the windows application log, etc depending on how robust you want it (maybe you remove the try catch if you don't want to bother with it).

    Here is the code:

    using System;

    using System.Collections.Generic;

    using System.Text;

    using System.Net.Mail;

    using System.Net;

    namespace ServiceEmail

    {

    class Program

    {

    static void Main(string[] args)

    {

    try

    {

    MailMessage msg = new MailMessage("from@email.com", "to@email.com");

    msg.Subject = "Service Failure Notification";

    msg.Body = "The follwing service has failed and is no longer running: " + args[0];

    SmtpClient client = new SmtpClient("smtp.mailserver.com");

    client.Credentials = CredentialCache.DefaultNetworkCredentials;

    client.Send(msg);

    }

    catch (Exception e)

    {

    Console.WriteLine("Error:" + e.Message.ToString());

    }

    }

    }

    }

    Hope it helps.

  • September 8, 2008 at 3:22 pm

    #868753

    Thanks alot man

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

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