SQL Server Performance

Can an Alert be Written to let You Know if SQL Server Shut Off/On?

Discussion in 'Getting Started' started by lcerni, Aug 13, 2008.

  1. lcerni New Member

    We have a SQL Server 2000 instance on Windows Server 2003. Sometimes when there is a storm the Server will reboot itself. I would like to set up an alert to let me know. Can it be done? If an alert cannot be written to let you know sql server is unexpectantly shutting down, can an alert be written to let you know that it is restarting up?
  2. Luis Martin Moderator

    If the server is unexpectantly shut down, I don't think you have any chance to have some alert.
    Now, you can write some alert (simple one) running each hour (or something like that) to know what is going on. I know, that is no the best solution.
    BTW: What about battery supply (Sorry I don't know the exact English translation) with software to tell you: "i.e., in 20 minutes your server will shutdown" ?
  3. lcerni New Member

    I have put together this procedure which might meet my needs. The LAN team does not keep us up to date on issues with the Server and if there is an unexpected shut down for some reason I would like to know so that I can investigate and see if any processes were affected. I would probably schedule this at night to run every hour.
    CREATE PROCEDURE usp_util_server_reboot_test
    AS
    -- =============================================================================================
    -- Procedure Name: usp_util_server_reboot_test
    -- Description: This USP is used to send an email to the appropriate individuals informing
    -- them that the server has rebooted
    -- Written by: lcerni
    -- Created: 08/14/2008
    --
    -- Last Updated:
    -- 20080814 lcerni - Original Code
    -- ==============================================================================================
    BEGIN
    DECLARE
    @nbr_of_days INT
    SET @nbr_of_days = (SELECT DATEDIFF(day, MIN(login_time), getdate()) FROM master..sysprocesses)
    IF( @nbr_of_days <= 1 )
    BEGIN
    EXEC master..xp_sendmail @recipients = 'lcerni',
    --@query = 'SELECT DATEDIFF(day, MIN(login_time), getdate()) FROM master..sysprocesses',
    @subject = 'The Server Was Recently Rebooted',
    @message = 'The Server Was Recently Rebooted'
    END
    END

    GO
  4. Greg Larsen New Member

  5. preethi Member

    for restart event, you can write a stored procedure and make it auto run at startup.
  6. MichaelB Member

    its sad that we have to do this and that there arnt options to track. .. I know with 2008 you can , but only for 2008 servers.
  7. satya Moderator

    It is hard to control those natural disasters, but you must have an ALERTING mechanism in place by using MS tools themselves such as MOM or as suggested you have to perform a regular monitoring with the scripts to see if its alive or not.
  8. moh_hassan20 New Member

    As the server stopped accediantly, so no way to get alarm except you have a service in other computer that monitor that server. it can be simple application on client that check @@servername every 10 second, for example , and send email after threeshold level .

Share This Page