SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create Mirror Alerts for All Mirrored Databases


Create Mirror Alerts for All Mirrored Databases

Author
Message
jordonpilling
jordonpilling
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 451
Comments posted to this topic are about the item Create Mirror Alerts for All Mirrored Databases

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

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....
varun_3g
varun_3g
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 324
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
jordonpilling
jordonpilling
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 451
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....
echoScout
echoScout
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 281
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 sp_altermessage:


/* Unsent Log Threshold (http://msdn.microsoft.com/en-us/library/aa337325.aspx) */
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
jordonpilling
jordonpilling
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 451
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....
echoScout
echoScout
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 281
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_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_time
DATABASE1 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



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



David
jordonpilling
jordonpilling
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 451
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....
jordonpilling
jordonpilling
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 451
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>Wink.

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>Wink 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 Smile

Regards

Jordon

Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
echoScout
echoScout
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 281
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
jordonpilling
jordonpilling
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 451
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....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search