Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How can we setup alert when sqlservver instance went down or services not started?

Answered How can we setup alert when sqlservver instance went down or services not started?

  • Thursday, March 22, 2012 4:09 PM
     
     
    Sign In to Vote
    0

    Hi

    How can we setup an alert when sqlserver instance went down or services not started(set as Automatic)?

    Thanks,

    Ron.

     

All Replies

  • Monday, March 26, 2012 4:30 AM
    Moderator
     
     Answered
    Sign In to Vote
    0
    Hi Ron,

    Once the SQL Server instance is stopped or paused, you cannot make use of any features within this instance, such as notification service to alert the status.
    In this case, you may consider Powershell to monitor SQL Server service. Please see: Monitoring SQL Server Service with WMI Events and ShowUI. In addition, you send the email with output via powershell. Here is an example: Sending email using Powershell script.

    Stephanie Lv

    TechNet Community Support

     
  • Tuesday, June 26, 2012 12:00 PM
     
     
    Sign In to Vote
    0

    How can we do this on a specific event ?

    Suppose if i want to fire an email only if a service is down or stopped.

    Rgds, Minesh

     
  • Monday, July 09, 2012 10:44 AM
     
     
    Sign In to Vote
    0

    I have several examples to use Powershell  WMI/WMI for Server Events and on Simple-Talk

    http://shellyourexperience.wordpress.com

    http://www.simple-talk.com/author/laerte-junior/

    But you can easily use it in SQL WMI Alerts too.

    An example to SQL Server Stops

    SQL WMI Alert

    Runspace '\\.\root\CIMV2'

    NAmed Instance INST1

    WQL = "select * from __InstanceModificationEvent within 5 where TargetInstance ISA 'Win32_Service' and TargetInstance.Name='MSSQL$INST1' and TargetInstance.State='Stopped'"

    Default Instance

    WQL = "select * from __InstanceModificationEvent within 5 where TargetInstance ISA 'Win32_Service' and TargetInstance.Name='MSSQLSERVER' and TargetInstance.State='Stopped'"

    Then you can configure a operator or run a job to send the email

    Powershell

    $Query = "selec$Query = "select * from __InstanceModificationEvent within 5 where TargetInstance ISA 'Win32_Service' and TargetInstance.Name='MSSQL`$`INST1' and TargetInstance.State='Stopped'"
    Register-WMIEvent  -ComputerName "ObiWan" `
         -Query $query `
         -Action {  DoSomething
                    } 

    You need any help, please ping me on laertejuniordba@hotmail.com

    :)