0

Testing I have completed to date seems to indicate that the following Threshold Alerts need to be defined for the <all databases> Database Name rather than a single named database:

  • Unsent Log Threshold (Error Message 32042)
  • Oldest Unsent Transaction Threshold (Error Message 32040)
  • Unrestored Log Threshold (Error Message 32043)
  • Mirror Commit Overhead Threshold (Error Message 32044)

This is in contrast to the documentation provided by Microsoft:

I have confirmed that Error Numbers 32040, 32042, 32043 and 32044 are being written to the SQL Server Log, as per sp_altermessage:

/* Unsent Log Threshold (http://msdn.microsoft.com/en-us/library/aa337325.aspx) */
sp_altermessage 32042, 'WITH_LOG', TRUE

If you were successful, I would appreciate if you describe the actions you took to cause Threshold Alerts to occur for an individual database.

Thank you.

EngineeringSQL
  • 275
  • 1
  • 2
  • 8
  • Now posted on Microsoft Connect: Alerting on Database Mirroring Events https://connect.microsoft.com/SQLServer/feedback/details/657230/alerting-on-database-mirroring-events – EngineeringSQL Apr 04 '11 at 19:52

1 Answers1

1

I’m assuming based on your listing that your alerts are firing when you select all databases, but not firing when selecting specific database.

The flow of the database monitoring SQL Agent job is as follows:

  1. Executes sys.dbmonitorupdate periodically (for example – once a minute). This call does the following:
  2. Retrieves values from sys.dm_os_performance_counters
  3. Inserts into msdb.dbo.dbm_monitor_data
  4. When a threshold is reached, an error is raised
  5. Regarding the alerts, it may not fire because sp_dbmmonitorupdate is in the context of msdb. So you don’t get a fire for individual databases, but you will for all databases.

I don't know if this is the universal behavior or was fixed later on - but I did see a case of this. So if this describes your situation and don’t want to use “all databases” – then you’ll need to either write your own detection procedure and job to get around this (might be other solutions too – but you don’t have the option to change the aforementioned system stored procedures and still keep things supported).

Joe Sack
  • 321
  • 1
  • 5