Create Mirror Alerts for All Mirrored Databases

  • Comments posted to this topic are about the item Create Mirror Alerts for All Mirrored Databases[/url]

    EDIT: (dont flame me for using cursors 😀 They serve their purpose for 1-off scripts such as this :cool:)

    EDIT: I have submitted a revised script (you may need to wait for it to be approved) to address the issues raised here. The new version is far more intelligent:

    - it will create the operator (make sure you enter the correct email address)

    - it will create an SP to collate Mirror Status Info and send via email in HTML (usp_dbmmonitorresults_EmailMode)

    - it will create a Job that will be ran when the Threshold alerts fire (Job: [DBM: Email Mirror Status])

    - the threshold alerts are now created just the once, and not for each database. When the threshold alerts run, they will auto run the job mentioned above.

    - Install is all automated from job creation and Alert to Job linking etc - just run it on the msdb after replacing "XXXXX@YYYY.COM" with your email address

    Knock Knock, Who's There?, sp underscore, sp underscore who?
    spid1, spid2, spid3....

  • Hi Jordon,

    Thx for such a great script, I am not clear about the thresholds do we still need to set the thresholds for the alerts ?

    Thanks

    varun

  • Hi Varun

    The script creates several different alerts. Alerts such as "Mirror Connection Lost" and "Principal Running Exposed" etc dont require thresholds, however, alerts such as "Unrestored Log Threshold" do.

    The SP sp_dbmmonitorchangealert can be used to set these thresholds (1 database at a time), I left this out of the script as thresholds are really different, and vary based on business preference and your mirroring requirements.

    However, if there is a demand for it, i will gladly extend the script to include these thresholds based on some variables in the script etc.

    Regards

    Jordon

    Knock Knock, Who's There?, sp underscore, sp underscore who?
    spid1, spid2, spid3....

  • Hello Jordon,

    I have read your article and reviewed your script (thank you for creating it), and the MSDN/Technet articles used as source.

    I have a comment and a question about Alerts relating to Error Numbers 32040, 32042, 32043 and 32044:

    Comment

    Your script assumes that Error Numbers 32040, 32042, 32043 and 32044 are already being written to the SQL Server Log.

    If not, this can be accomplished using [font="Courier New"]sp_altermessage[/font]:

    /* Unsent Log Threshold [font="Courier New"](http://msdn.microsoft.com/en-us/library/aa337325.aspx)[/font] */

    sp_altermessage 32042, 'WITH_LOG', TRUE

    Question

    With respect to the Unsent Log Threshold (Error Number 32042) Alert defined for a specific database, are you able to cause the Alert to occur? Testing I have completed to date seems to indicate that Threshold Alerts need to be defined for the <all databases> Database Name rather than a single named database. If you were successful, I would appreciate if you update this article with the actions you took to cause Threshold Alerts to occur for an individual database.

    Thank you.

    David

  • Hi David

    As i say in my second post above, you simply call sp_dbmmonitorchangealert to set thresholds at a Database level. The very first paramater of sp_dbmmonitorchangealert is to specify the target database.

    Example from MSDN:

    EXEC sp_dbmmonitorchangealert AdventureWorks2008R2, 1, 30, 1 ;

    EXEC sp_dbmmonitorchangealert AdventureWorks2008R2, 2, 10000, 1 ;

    EXEC sp_dbmmonitorchangealert AdventureWorks2008R2, 3, 10000, 1 ;

    EXEC sp_dbmmonitorchangealert AdventureWorks2008R2, 4, 1000, 0 ;

    EXEC sp_dbmmonitorchangealert AdventureWorks2008R2, 5, 8, 1 ;

    Hope that helps

    Regards

    Jordon

    Knock Knock, Who's There?, sp underscore, sp underscore who?
    spid1, spid2, spid3....

  • Hi Jordon

    I understood that part. The thresholds are already in place.

    However, when one of the thresholds (e.g. Unsent Log) is exceeded for an extended period of time, the event is written to the log, however no email is sent to the Operator. I think that this is because this event (32042) is being logged as a server-level event and not a database-level event.

    And if you look at the Properties of the Alert, specifically in the History, the Number of occurrences equals 0, the Date of last alert is "(Never occurred)" and the Date of last response is "(Never responded)".

    Thoughts?

    database_namerolemirroring_statewitness_statuslog_generation_rateunsent_logsend_rateunrestored_logrecovery_ratetransaction_delaytransactions_per_secaverage_delaytime_recordedtime_behindlocal_time

    DATABASE1140265679332296570055702011-03-25 16:07:22.6102011-03-25 16:07:00.6432011-03-25 12:07:22.610

    Message

    The alert for 'unsent log' has been raised. The current value of '7117' surpasses the threshold '1'.

    David

  • hmmm, I understand you now.

    Let me do some stress testing and get back to you shortly.

    Regards

    Jordon

    Knock Knock, Who's There?, sp underscore, sp underscore who?
    spid1, spid2, spid3....

  • Hi David,

    Bad AND Good news!

    I was able to recreate your problem, event 32040 etc are indeed written to event log, and no email is generated. As you suggest, this is because my script sets the "Database Name" property on the Alert.

    Ive had a look on MS Connect and this seems to be a popular problem, for example:

    https://connect.microsoft.com/SQLServer/feedback/details/266658/mirroring-monitoring-issues

    The lack of a database name in the event means SQL Server cannot bind the alert.

    There is however, a workaround (and good news). You could delete all threshold alerts and create 1 for all databases (just set the database name property to <all databases>).

    That way, you would get an email alert, and with the alert firing (ive tested this, the event definitly does fire when using <all databases>) you can edit the event to run a SQL job (see sp_update_alert - the @job_id paramater),

    the job in question could run a simple SP that executes the following for each database and collates and emails the results:

    use msdb

    exec sys.sp_dbmmonitorresults @database_name=N'synergy', @mode = 0, @update_table = 1

    That way, when a threshold is exceeded, you will receive an email telling you which threshold has been exceeded, and a 2nd email detailing which database is causing the problem.

    I will revise my script with this behaviour at some point, but dont hold your breath, i am extremely busy at the moment.

    Hope that makes sense 🙂

    Regards

    Jordon

    Knock Knock, Who's There?, sp underscore, sp underscore who?
    spid1, spid2, spid3....

  • Hi Jordon,

    Your solution is what I planned to do as well (unless there was a configuration that allowed Alert definitions at a database level).

    So, the Event Alert Definition for Errors 32040, 32042, 32043 and 32044 must not be created for individual databases.

    I suspect that the Alert Definition has been built to be flexible (e.g. some Errors are caught by SQL Server Event Alerts at a database level).

    I'll post on Microsoft Connect, ServerFault.com as well as here a request to each community to find out if anyone has compiled a list of which Errors are and are not caught by SQL Server Event Alerts at a database level.

    Thank you very much for responding to this so promptly. Good luck on completing your workload!

    David

  • Hi David,

    Sorry i could not be of more use, i fear this problem lies deep within the SQL event logging binaries, i couldn't find no reference of 32040 etc within the master or msdb databases (by scripting out all objects and data), which would imply the event ID's are defined within assemblies or binaries.

    Let me know what you find out, this issue is indeed annoying and many on Connect are waiting for the same solution.

    Regards

    Jordon

    EDIT: I have submitted a revised script (you may need to wait for it to be approved) to address the issues raised here. The new version is far more intelligent:

    - it will create the operator (make sure you enter the correct email address)

    - it will create an SP to collate Mirror Status Info and send via email in HTML (usp_dbmmonitorresults_EmailMode)

    - it will create a Job that will be ran when the Threshold alerts fire (Job: [DBM: Email Mirror Status])

    - the threshold alerts are now created just the once, and not for each database. When the threshold alerts run, they will auto run the job mentioned above.

    - Install is all automated from job creation and Alert to Job linking etc - just run it on the msdb after replacing "XXXXX@YYYY.COM" with your email address

    Knock Knock, Who's There?, sp underscore, sp underscore who?
    spid1, spid2, spid3....

  • Finally posted on Microsoft Connect:

    https://connect.microsoft.com/SQLServer/feedback/details/657230/alerting-on-database-mirroring-events

    If you know anyone else who has worked with SQL Server database mirroring, please ask them to add to the feedback @Microsoft Connect.

  • Hi Jordon, someone early in this thread has thanked you for your new script. But I couldn't find that script, would you please guide me to your script!

  • Hi Praveen, the script is posted under scripts, there is a link the script in the first post of this thread:

    http://www.sqlservercentral.com/Forums/FindPost1029139.aspx

    Regards

    Jordon

    Knock Knock, Who's There?, sp underscore, sp underscore who?
    spid1, spid2, spid3....

  • HI Jordan, I had previously run your script too. Yet again I did it in my Principal Server. Is there any step left to do 'coz I haven't received any mail regarding Mirroring Status yet? You've mentioned to enter DBA email address. I don't get it exactly. Please do guide me on this matter.

    FYI: Database Mail: sqluser

    and I've put my email id under Admins_MirrorAlerts

  • Thanks for the script.

Viewing 15 posts - 1 through 14 (of 14 total)

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