Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Create Mirror Alerts for All Mirrored Databases Expand / Collapse
Author
Message
Posted Thursday, December 2, 2010 3:38 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 3, 2014 7:32 AM
Points: 50, Visits: 399
Comments posted to this topic are about the item Create Mirror Alerts for All Mirrored Databases

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

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....
Post #1029139
Posted Monday, December 13, 2010 2:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 31, 2012 2:53 PM
Points: 13, 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
Post #1034082
Posted Monday, December 13, 2010 3:00 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 3, 2014 7:32 AM
Points: 50, Visits: 399
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....
Post #1034096
Posted Thursday, March 24, 2011 11:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 15, 2013 8:27 PM
Points: 7, Visits: 260
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
Post #1083484
Posted Friday, March 25, 2011 9:58 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 3, 2014 7:32 AM
Points: 50, Visits: 399
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....
Post #1084147
Posted Friday, March 25, 2011 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 15, 2013 8:27 PM
Points: 7, Visits: 260
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
Post #1084174
Posted Friday, March 25, 2011 10:32 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 3, 2014 7:32 AM
Points: 50, Visits: 399
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....
Post #1084182
Posted Friday, March 25, 2011 12:13 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 3, 2014 7:32 AM
Points: 50, Visits: 399
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....
Post #1084273
Posted Friday, March 25, 2011 12:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 15, 2013 8:27 PM
Points: 7, Visits: 260
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
Post #1084290
Posted Monday, March 28, 2011 3:10 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 3, 2014 7:32 AM
Points: 50, Visits: 399
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....
Post #1084785
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse