InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 01:25:30 GMT20http://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspxHI 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_MirrorAlertsTue, 06 Sep 2011 01:32:19 GMTpraveen.singhhttp://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspxHi Praveen, the script is posted under scripts, there is a link the script in the first post of this thread:[url]http://www.sqlservercentral.com/Forums/FindPost1029139.aspx[/url]RegardsJordonMon, 05 Sep 2011 07:43:16 GMTjordonpillinghttp://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspxHi 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!Mon, 05 Sep 2011 04:38:13 GMTpraveen.singhhttp://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspxFinally posted on Microsoft Connect:[url=https://connect.microsoft.com/SQLServer/feedback/details/657230/alerting-on-database-mirroring-events]https://connect.microsoft.com/SQLServer/feedback/details/657230/alerting-on-database-mirroring-events[/url]If you know anyone else who has worked with SQL Server database mirroring, please ask them to add to the feedback @Microsoft Connect.Mon, 04 Apr 2011 13:38:33 GMTechoScouthttp://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspxHi 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.RegardsJordon[color="#ff0000"][b]EDIT: I have submitted a revised script ([u]you may need to wait for it to be approved[/u]) 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[/b][/color]Mon, 28 Mar 2011 03:10:49 GMTjordonpillinghttp://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspxHi 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 [u]are[/u] 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!DavidFri, 25 Mar 2011 12:35:36 GMTechoScouthttp://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspxHi 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:[url]https://connect.microsoft.com/SQLServer/feedback/details/266658/mirroring-monitoring-issues[/url]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:[code="sql"]use msdbexec sys.sp_dbmmonitorresults @database_name=N'synergy', @mode = 0, @update_table = 1[/code]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 :)RegardsJordonFri, 25 Mar 2011 12:13:40 GMTjordonpillinghttp://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspxhmmm, I understand you now.Let me do some stress testing and get back to you shortly.RegardsJordonFri, 25 Mar 2011 10:32:27 GMTjordonpillinghttp://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspxHi JordonI 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?[code="sql"]database_name role mirroring_state witness_status log_generation_rate unsent_log send_rate unrestored_log recovery_rate transaction_delay transactions_per_sec average_delay time_recorded time_behind local_timeDATABASE1 1 4 0 2656 7933 2296 57 0 0 557 0 2011-03-25 16:07:22.610 2011-03-25 16:07:00.643 2011-03-25 12:07:22.610[/code][code="sql"]MessageThe alert for 'unsent log' has been raised. The current value of '7117' surpasses the threshold '1'.[/code]DavidFri, 25 Mar 2011 10:19:38 GMTechoScouthttp://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspxHi DavidAs i say in my second post above, you simply call [url=http://msdn.microsoft.com/en-us/library/ms365376.aspx]sp_dbmmonitorchangealert[/url] to set thresholds at a Database level. The very first paramater of [url=http://msdn.microsoft.com/en-us/library/ms365376.aspx]sp_dbmmonitorchangealert[/url] is to specify the target database.Example from MSDN:[code="sql"]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 ;[/code]Hope that helpsRegardsJordonFri, 25 Mar 2011 09:58:33 GMTjordonpillinghttp://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspxHello 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:[b][u]Comment[/u][/b]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 [url=http://msdn.microsoft.com/en-us/library/ms175094.aspx][font="Courier New"][size="3"][b]sp_altermessage[/b][/size][/font][/url]:[code="sql"]/* Unsent Log Threshold [url=http://msdn.microsoft.com/en-us/library/aa337325.aspx][font="Courier New"][size="2"](http://msdn.microsoft.com/en-us/library/aa337325.aspx)[/size][/font][/url] */sp_altermessage 32042, 'WITH_LOG', TRUE[/code][b][u]Question[/u][/b]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 [i]<all databases>[/i] 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.DavidThu, 24 Mar 2011 11:21:27 GMTechoScouthttp://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspxHi VarunThe 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 [url=http://msdn.microsoft.com/en-us/library/ms365376.aspx]sp_dbmmonitorchangealert[/url] 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.RegardsJordonMon, 13 Dec 2010 15:00:40 GMTjordonpillinghttp://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspxHi 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 ?ThanksvarunMon, 13 Dec 2010 14:35:08 GMTvarun_3ghttp://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspxComments posted to this topic are about the item [b][url=http://www.sqlservercentral.com/scripts/alerts/71731/]Create Mirror Alerts for All Mirrored Databases[/url][/b]EDIT: (dont flame me for using cursors :-D They serve their purpose for 1-off scripts such as this :cool:)[color="#ff0000"][b]EDIT: I have submitted a revised script ([u]you may need to wait for it to be approved[/u]) 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[/b][/color]Thu, 02 Dec 2010 03:38:26 GMTjordonpilling