sp_dbmmonitorchangealert (Transact-SQL)
TOC
Collapse the table of content
Expand the table of content

sp_dbmmonitorchangealert (Transact-SQL)

 

Adds or changes warning threshold for a specified mirroring performance metric.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

  
sp_dbmmonitorchangealert database_name   
    , alert_id   
    , alert_threshold   
    , enabled   

database_name
Specifies the database for which to add or change the specified warning threshold.

alert_id
An integer value that identifies the warning to be added or changed. Specify one of the following values:

ValuePerformance metricWarning threshold
1Oldest unsent transactionSpecifies the number of minutes worth of transactions that can accumulate in the send queue before a warning is generated on the principal server instance. This warning helps measure the potential for data loss in terms of time, and it is particularly relevant for high-performance mode. However, the warning is also relevant for high-safety mode when mirroring is paused or suspended because the partners become disconnected.
2Unsent logSpecifies how many kilobytes (KB) of unsent log generate a warning on the principal server instance. This warning helps measure the potential for data loss in terms of KB, and it is particularly relevant for high-performance mode. However, the warning is also relevant for high-safety mode when mirroring is paused or suspended because the partners become disconnected.
3Unrestored logSpecifies how many KB of unrestored log generate a warning on the mirror server instance. This warning helps measure failover time. Failover time consists mainly of the time that the former mirror server requires to roll forward any log remaining in its redo queue, plus a short additional time.
4Mirror commit overheadSpecifies the number of milliseconds of average delay per transaction that are tolerated before a warning is generated on the principal server. This delay is the amount of overhead incurred while the principal server instance waits for the mirror server instance to write the transaction's log record into the redo queue. This value is relevant only in high-safety mode.
5Retention periodMetadata that controls how long rows in the database mirroring status table are preserved.

For information about the event IDs corresponding to the warnings, see Use Warning Thresholds and Alerts on Mirroring Performance Metrics (SQL Server).

alert_threshold
The threshold value for the warning. If a value above this threshold is returned when the mirroring status is updated, an entry is entered into the Windows event log. This value represents KB, minutes, or milliseconds, depending on the performance metric.

System_CAPS_ICON_note.jpg Note


To view the current values, run the sp_dbmmonitorresults stored procedure.

enabled
Is the warning enabled?

0 = Warning is disabled.

1 = Warning is enabled.

System_CAPS_ICON_note.jpg Note


Retention period is always enabled.

None

None

Requires membership in the sysadmin fixed server role.

The following example sets thresholds for each of the performance metrics and the retention period for the AdventureWorks2012 database. The following table shows the values used in the example.

alert_idPerformance metricWarning thresholdIs the warning enabled?
1Oldest unsent transaction30 minutesYes
2Unsent log10,000 KBYes
3Unrestored log10,000 KBYes
4Mirror commit overhead1,000 millisecondsNo
5Retention period8 hoursYes
EXEC sp_dbmmonitorchangealert AdventureWorks2012, 1, 30, 1 ;  
EXEC sp_dbmmonitorchangealert AdventureWorks2012, 2, 10000, 1 ;  
EXEC sp_dbmmonitorchangealert AdventureWorks2012, 3, 10000, 1 ;  
EXEC sp_dbmmonitorchangealert AdventureWorks2012, 4, 1000, 0 ;  
EXEC sp_dbmmonitorchangealert AdventureWorks2012, 5, 8, 1 ;  

Monitoring Database Mirroring (SQL Server)
sp_dbmmonitorhelpalert (Transact-SQL)
sp_dbmmonitordropalert (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft