Using WMI alert for mirroring in Sql server 2012

  • We are using mirroring in Sql Server 2012 and we are using WMI alerts for some of events such as Connection lost,Manual failover,...

    In alert definition we have set the followings:

    Type: WMI event alert

    NameSpace: \\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER

    Query: select * from DATABASE_MIRRORING_STATE_CHANGE where State = 6

    The alert is bing triggered correctly,but on email description instead of having meaningful explaination like 'Connection lost',We have the same query:

    Query: select * from DATABASE_MIRRORING_STATE_CHANGE where State = 6

    We have used the same implementation in our Sql Server 2008 servers and it is working fine.This mashine (Sql Server 2012) is a new mashine.

    I would appreciate if someone help me on this.AlsoI checked and 'Include body of e-mail in notification message' was already set.

    Thanks

    Aspet

  • The alerts are slightly different in 2012, if you right click your alert, go to properties and then Options you are presented with a free text field to add any "Additional notification message to send" and whether to include the additional text in email, pager or net send notifications.

    Hope this helps!

    Tony

  • I realise this is a very old thread, however I've just come up against the same issue.  

    I'm running mirrored databases on a 11.0.2100 build.  There are Alerts set for various mirroring state changes.  The alerts fire successfully.  I can get out certain data in the response job such as ESCAPE_SQUOTE(WMI(DatabaseName)), however the ESCAPE_SQUOTE(A-MSG) returns the actual WMI query text in the alert instead of the actual message set in the "Additional notification message to send" box.  E.g for ESCAPE_SQUOTE(A-MSG) it returns SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 9 AND DatabaseName = 'SalesDB' 

    Which appears to be wrong.   Help!

    -Dean.

Viewing 3 posts - 1 through 2 (of 2 total)

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