Post reply

How do I find out when SQL Server has stopped?

  • December 13, 2004 at 5:03 am

    #85027

    Short of all the support calls obviously?

    A client phoned saying our application could not connect into SQL Server and the reason was SQL Server had stopped at some point over the weekend. We restarted SQL and they are happy but I want to know why SQL server stopped.

    Application Log had nothing and SQL Logs had nothing as to when or why.

    Does anyone know how to log this information, or what tools can be used to alert someone when SQL Stops?

    Thanks in advance.

    Keith

  • December 13, 2004 at 5:10 am

    #533484

    We built a small home grown tool that will

    1. Ping the server that SQL Server is sitting on.

    2. Run a query to determine if agent is running or in your case connect to the DB and run a simple query.

     

    If either one of these fails have the softare send out an email. Run the software from a machine other then the SQL Server.

  • December 13, 2004 at 9:04 am

    #533570

    If you have monitoring software like <cough cough> Unicenter that checks services, HDD free size etc...  you could use that to tell if the SQL server service dies too....  and YES I do know that Unicenter is a mainframe tool.  Please don't ask...

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • December 13, 2004 at 9:27 am

    #533574

    Thanks for the advice but Unicenter seems like a big hammer to crack a small nut and beyond the pocket of our client just to monitor one instance of SQL Server.

    I found something called MS SQL Server 2000 Management Pack guide http://www.microsoft.com/technet/prodtechnol/mom/mom2005/maintain/sqlservermpguide.mspx

    but I think this has the same issue as Unicenter

    Maybe we will have to look at the doing a similar thing to vbKevin with the home grown solution, or go for fix D which is to install Service Manager on the administrators computer and when it goes red, there is a problem.

    Thanks for your help,

    Keith

  • December 13, 2004 at 10:13 am

    #533583

    In services, you should be able to set services to restart if they fail. On the recovery tab, you get 3 chances, the last could be to run a file.

  • December 13, 2004 at 10:42 am

    #533595

    ...and if I run a file I could do something very simple like net send so they get an immediate alert when it stops, as well as something to log when it happened.

    Thanks for that, I never realised that was an option within services.

    Keith

  • December 13, 2004 at 4:27 pm

    #533670

    Perhaps create an alert for Error Number 14265 or 17147 or...or...or and notify one or more of your operators?

    Now my problem is how to notify me when the database is back up again.   I can test for Error number 17126 which does get written to the event log...but it appears as though since the Agent is dependent on the database...it's not actually up and running when this error occurs.   Can anyone help me with THAT?

    Art

     

  • December 14, 2004 at 1:48 am

    #533697

    Have you tried looking at Big Brother??

    http://www.bb4.org/

     

    It should do a lot of the things that you require & it is FREE!!

    There are many scripts that you can add to it to increase the functionality

    Have a look

     

    Regards

    Andy

  • December 14, 2004 at 3:00 am

    #533714

    Art,

    When you say using an alert on an error number, do you mean a SQL Agent alert, or another type? SQL Agents won't work because the SQL Server instance is down, therefore the Agent will be down to. If it is another type of alerter, can you let me know what type?

    As for alerter when the database is up again, I beleive I saw an article in this site two or three days ago on firing a stored procedure when the instance starts up. Have a search.

    Andy,

    Big Brother looks good as it is free, which is my favorite price! I have used Quest software before and I always found their products very good to use so I will definitely be investigating that.

    Thanks,

    Keith

  • December 14, 2004 at 9:14 am

    #533785

    Keith,

    Yes,  I'm talking about a SQL Agent Alert.   I sure wish the Agent could run independently of the database....kind of like we used to have to do in SQL 6.5...setting up our alerts in the Perfmon.

    We DO have a separate monitoring tool called ipMonitor which does catch the up/down events.  I'd just like to be able to do it with the MS tools.

    Thanks!

    Art

  • December 14, 2004 at 9:38 am

    #533792

    Low end solution, Servers Alive (http://www.woodstone.nu/salive/). I used to use it and it worked great. Drop it on a workstation and set it to alert you.

  • December 14, 2004 at 5:00 pm

    #533868

    Art

    Take a look at my home grown ASP page over at the Australian SQL Server User Group.

    http://www.sqlserver.com.au/resources/ViewResource.aspx?resourceId=9

    We use this page in conjunction with Servers Alive!

    http://www.woodstone.nu/salive/

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

  • December 22, 2004 at 1:39 pm

    #534824

    We use products from http://www.purenetworking.net - sqlstripes or server monitor will help

Viewing 13 posts - 1 through 12 (of 12 total)

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